Results 1 to 14 of 14

Thread: DSUM (2002)

  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DSUM (2002)

    I am trying to create a report that shows the opening balance of a person (a person is identified by their national insurance number) and the closing balance of that same person. The start date and end date are 28 days apart. I have started the report - grouped it by the national insurance number and tried to put a dsum formula in the group header and footer, the formula I have used is =DSum(" [zTestingClientTrans]![Amount]","[zTestingClientTrans]","[ProcessDate] <#01/08/05#")

    I have tried to attached a stripped down version of the database to illustrate but the file is too big. How do you do that? I tried zipping as well.

    As always I would be very grateful if someone could point me in the right direction

    Amanda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSUM (2002)

    You haven't told us what your problem actually is. I notice that there is a space after the first quote. Try this:

    =DSum("[Amount]","[zTestingClientTrans]","[ProcessDate] <#01/08/05#")

    See <post#=401925>post 401925</post#> for instructions on creating a stripped down copy of the database for posting.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSUM (2002)

    Hans,

    Yes, I realize it wasn't very clear. I was trying to attach a file to demonstrate. Apologies.
    The problem is that for each person I have the same value, this should not be the case.
    I thought that because the formula was in the group header, it would automatically just calculate for that one person.

    Many thanks

  4. #4
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSUM (2002)

    Forgot to mention - I did try removing the space, but that didn't change anything. I don't get an error just the same value (of 10) over and over again.

    Thanks

    Amanda

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSUM (2002)

    To display the value for the current person, you must expand the 3rd argument of DSum (the WhereCondition):

    =DSum("[Amount]","[zTestingClientTrans]","[ProcessDate] < #01/08/05# AND [NIN] = " & [NIN])

    where NIN is the name of the National Insurance Number field. The above assumes that this is a number field. If it is a text field, the expression becomes

    =DSum("[Amount]","[zTestingClientTrans]","[ProcessDate] < #01/08/05# AND [NIN] = " & Chr(34) & [NIN] & Chr(34))

    Chr(34) is the code for the <code>"</code> character.

  6. #6
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSUM (2002)

    Thanks Hans

    I know have
    =DSum("[zTestingClientTrans]![Amount]","[zTestingClientTrans]","[ProcessDate] < #01/08/05# AND [Ni_Number] = " & Chr(34) & [NI_number] & Chr(34))
    However there is nothing at all now displaying on my report.

    Amanda

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSUM (2002)

    Perhaps you could attach a stripped down and zipped copy of the database. I posted a link to the instructions higher up in this thread.

  8. #8
    Lounger
    Join Date
    Feb 2005
    Location
    Florida, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSUM (2002)

    >> a person is identified by their national insurance number <<

    Is it a numeric field?

    -tivoli0

  9. #9
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSUM (2002)

    Hans,

    Please find attached file. Many thanks for looking into this for me.

    Amanda

  10. #10
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSUM (2002)

    No, the national insurance number is actually a text field.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSUM (2002)

    The problem here is the difference between US and UK date format. From the data in your table, I gather that you want to sum amounts whose process date is before the 1st of August. However, 01/08/05 is interpreted by SQL as the 8th of January, and since there are no records from January or earlier, the DSum is blank (null). So try

    =DSum("[zTestingClientTrans]![Amount]","[zTestingClientTrans]","[ProcessDate] < #08/01/05# AND [Ni_Number] = " & Chr(34) & [NI_number] & Chr(34))

  12. #12
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSUM (2002)

    Hans,

    You are a genius! thank you so much.
    So sql in functions in reports uses american date format, although sql in stored queries can use uk date format. Do you know of any guidelines of when to use which format - or is it just a case of testing each scenario?

    Many thanks

    Amanda

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSUM (2002)

    When you view a query in design view, you can use the local (UK) format. The Access interface converts it to US format for you behind the screens.

    When you view a query in SQL view, and when you write VBA code, you must use US format. The same holds for the WhereCondition argument in DSum etc., since this is processed by SQL, not by the Access interface.

  14. #14
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSUM (2002)

    Thanks again Hans.

    Have a good weekend

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •