Results 1 to 4 of 4
  1. #1
    DWinMadison is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2021
    Posts
    4

    dsum in query with 2 criteria (1 is a date). Calculates, but not correctly.

    I have an aggregate query in which I need to have a total and a running total. The query results are not correct. Any assistance would be appreciated.
    The ultimate goal is to create an active graph on a form for each metric that will show the performance on discreet data by month but also reflect a running total across the year. The waters get muddy as our fiscal year runs October - September, so I cannot break on clean Dec 31.

    The fields are

    Query fields are:

    MetID (Alias: mID) - An autonumber representing a metric for which daily data is being collected. Grouped, ascending in query column 1
    datDate (Alias: dDate) - a shortdate date field representing the day of data entry formatted as format(datDate,'yyyy-mm') and Grouped, ascending in query column 2
    SumOfdatPoint - Summed daily data enties Summed in query column 3
    RunTot - a dsum expression attempting to create a running total by metric and month. an Expression in query column 4



    The dsum expression is:
    RunTOT: DSum("datPoint","tbstrdata","metID <= " & [mID] & "AND datDate <= format(#" & [dDate] & "#,'yyyy-mm')")

    Here's the underlying SQL:
    _________________
    SELECT tbStrData.metID AS mID, Format([datDate],'yyyy-mm') AS dDate, Sum(tbStrData.datPoint) AS SumOfdatPoint, DSum("datPoint","tbstrdata","metID <= " & [mID] & "AND datDate <= format(#" & [dDate] & "#,'yyyy-mm')") AS RunTOT
    FROM tbStrData
    GROUP BY tbStrData.metID, Format([datDate],'yyyy-mm')
    ORDER BY tbStrData.metID, Format([datDate],'yyyy-mm');
    _________________

    The results look like this...the total doesn't start until the subsequent line of each metric group and it's summing on the months instead of the metrics. Any suggestions?

    mID dDate SumOfdatPoint RunTOT
    25 2020-10 100.00
    25 2020-11 72.73 100
    25 2020-12 61.10 172.730003356934
    25 2021-01 70.37 233.830001831055
    26 2020-10 0.06
    26 2020-11 0.05 100.059999998659
    26 2020-12 0.04 172.840003356338
    26 2021-01 0.04 233.980001829565
    27 2020-10 84.95

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    try
    =DSum("datPoint","tbstrdata","[metID] <= " & [mID] & " AND [datDate] <= format([dDate],'yyyy-mm')")


    but that seems clumsy, so you could add the field to the query:
    YrMoDate: format([dDate] , "yyyy-mm")


    then just do the lookup:
    =DSum("datPoint","tbstrdata","[metID] <= " & [mID] & " AND [YrMoDate] <='" & txtYrMo & "'")

  3. #3
    DWinMadison is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2021
    Posts
    4
    Thanks for the suggestions. The underlying query field datDate was already formatted "yyyy-mm". In essence both the date fields are formatted to 'yyyy-mm' but the reference to the underlying date field must have the #datDate# format to return anything but an error message. I double checked to be sure the underlying query values (MetID and datDate) were to the left of the <= with the corresponding current query fields ([mID] and [dDate] to the right. I guess I could convert the dates to text strings instead of formatted dates, and see if that works.

  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Is datDate an actual date field or a text field?

    If it's actually a date then ignore the formatting (That is purely for display) and apply a normal criteria expression to it.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 6
    Last Post: 12-05-2019, 03:17 PM
  2. cant write correctly dsum func
    By nahum abramovich in forum Access
    Replies: 12
    Last Post: 06-04-2018, 01:37 AM
  3. Replies: 5
    Last Post: 01-20-2018, 03:40 PM
  4. Use the Date as a criteria in Dsum function
    By Ank_db in forum Queries
    Replies: 1
    Last Post: 01-18-2015, 12:08 PM
  5. DSUM with date criteria from form
    By krutoigoga in forum Reports
    Replies: 4
    Last Post: 07-28-2010, 01:32 PM

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
  •  
Other Forums: Microsoft Office Forums