Results 1 to 2 of 2

How to use a create a DSum with multiple criteria

  1. #1
    FlyingDisc is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2011

    How to use a create a DSum with multiple criteria

    am currently programming a report that needs to spit out all of the money paid to a specific client. The Dsum function I used was:

    =DSum("CashforVisit","Financials","Financials.SSN= " & [SSN])

    Which worked excellent. However, I need a way to add another criteria to the DSum function, specifically that the payments happenned in 2011. This report grabs information from table Financials, which documents every payment, the SSN of the recipient, and the date of the payment.

    Values to sum: CashforVisit
    Table name: Financials
    Criteria1:"Financials.SSN= " & [SSN]
    Criteria2: DateofVisit >= 1/1/2011
    Criteria3: DateofVisit <= 12/31.2011

    The following solution has been suggested, but unfortunately does not seem to work:
    =DSum("CashforVisit","Financials","Financials.SSN= " & [SSN] & " AND Financials.DateofVisit Between " & DateSerial(Year(Date()),1,1) & " And " & DateSerial(Year(Date()),12,31))

    This expression is in the detail field of a report.
    How would I do this? I have been trying for a while, I would love some help

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    give this a flyer:

    =DSum("CashforVisit","Financials","Financials.SSN= " & [SSN] & AND "Financials.DateofVisit >= " #1/1/2011# And "Financials.DateofVisit <= " #12/31/2011#)

    complete aircode - may not work.

    you sure you want to hard code dates? ....that almost is never a good idea (in my humble opinion...). You could have user select year in a form - and then call in that date range from the form as the first simple select query which returns all records for that date range. then set up your working dsum to run on this new query instead..... gives the user flexibility in their report dates/year and makes for a simpler dsum statement too.....

    happy 2011

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple Criteria in a string
    By cksm4 in forum Programming
    Replies: 3
    Last Post: 08-04-2010, 11:54 AM
  2. DSUM with date criteria from form
    By krutoigoga in forum Reports
    Replies: 4
    Last Post: 07-28-2010, 01:32 PM
  3. Replies: 1
    Last Post: 05-05-2010, 01:54 AM
  4. Query multiple field & criteria
    By fua in forum Access
    Replies: 2
    Last Post: 11-04-2009, 07:22 PM
  5. Multiple criteria query
    By DJ-Specter in forum Queries
    Replies: 1
    Last Post: 09-23-2009, 04:47 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Tech Forums: Microsoft Office Forums