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

    Dsum with >1 criteria (2002)

    Hi.

    I was wondering if I could do a dsum function with more than one set of criteria.
    for example I want to say:
    sum the amount field from the clientTransactions table where the transdate is less than 01/01/01 and the nino = tntest
    I am unsure of the syntax for this and where the And would go, the syntax that I have is
    =dsum([amount], [clientTransactions],[clientTransactions]!<transdate>>100 and [clientTransactions]![nino]= 'tntest')
    It would be great if somebody could let me know if I am on the right lines.

    Many thanks

    Amanda

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Dsum with >1 criteria (2002)

    You are on the right track, but you need to encase each part of the Dsum function in quotes, plus I don't know what the !>100 is supposed to be. It should be:

    =dsum("[amount]", "clientTransactions","transDate<#01/01/01# and [nino]= 'tntest' ")
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  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 with >1 criteria (2002)

    Thanks Mark

    The problem that I now have is that the formula uses the american date format and I am referring to values with dd/mm/yy date format. Do I have to code this to get round the problem?

    Amanda

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Dsum with >1 criteria (2002)

    I believe that when you hard-code a date in a query by encasing it between # signs, you must specifiy the date using the American format (mm/dd/yy, etc.). Not 100% sure about that, though. It won't matter that you normally display dates in d/m/y format, as it will still properly compare them.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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