Results 1 to 9 of 9
  1. #1
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94

    Help me with Dsum syntax for grouped running totals please

    I am trying to use Dsum to get a running total of a simple credit and debits table, grouped by customer code (which is a short text field). For this I need to incorporate my credit (Received) and debit fields (OutOthers) (OutWork) and (OutBookings) from the table called tblTestAccount.
    I have got it to work until I try to limit the records to those previous to the current month. The reason is to provide a running total, ordered by EntryDate so that the final line total for each customer can be used as the opening balance for the following month's account (this current month's).
    This works;
    CBal: DSum("[Received]-[OutOthers]-[OutBookings]-[Outwork]","tblTestAccount","[CusCode]='" & [CusCode] & "'").
    When I add;
    And "[EntryDate] <=#" & DateSerial(Year(Date()),Month(Date())-1,1) & "#"
    It fails.
    In other words, the query returns all the lines for each customer with the correct 'final' line balance which will be my next opening balance in the report, but when I try to limit the results to the previous month, the rsults for all of the lines is the same, total.


    Thanks for helping. Click image for larger version. 

Name:	Screenshot (61).jpg 
Views:	18 
Size:	111.7 KB 
ID:	34461

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Maybe-
    & " AND EntryDate" <= DateSerial(Year(Date), Month(Date) - 1, 1))
    OR
    & " AND [EntryDate] <=" & DateSerial(Year(Date), Month(Date) - 1, 1))

    Well, I set up a test table to actually test these and NEITHER works!
    I also couldn't get the datediff function to accept [EntryDate] as an argument.

    Anyone else want to jump in?
    Last edited by davegri; 06-17-2018 at 04:29 PM. Reason: stumped

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How did you add it? The And has to be inside the quotes, after the single quote.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    Thank you both,
    I added the extra code after the double, single, double quotes and before the final parenthesis.
    So now I've tried the suggestion that Davegri made, with all the permutations I can think of. Am I understanding properly because my head is awash with quotes, hashes and ampersands?
    This is one of the possibilities that I've tried and the result is 'contains invalid syntax;
    CBal: DSum("[Received]-[OutOthers]-[OutBookings]-[Outwork]","tblTestAccount","[CusCode]='" & [CusCode] & "'" & " AND EntryDate">=DateSerial(Year([Date]),Month([Date])-1,1))
    I can't see that double, single, double quotes " & " AND... is correct, must be me misunderstanding.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The double quotes after EntryDate need to be deleted, and you need the # delimiters you had.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Trevor
    As pbaldy has already stated it should be:
    Code:
    CBal: DSum("[Received]-[OutOthers]-[OutBookings]-[Outwork]","tblTestAccount","[CusCode]='" & [CusCode] & "' And [EntryDate]>=#" & DateSerial(Year(Date()),Month(Date())-1,1) & "#")
    However I'm not sure how this fits in with other items you've posted recently
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I must not have stated it well enough.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Paul,
    PMFJI - I'd written my reply and it was only as I posted it that I saw your answer ....so I added the first sentence.
    The final line was because the OP has also contacted me privately
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    Thank you all for your help. I had to do a little juggling and change the expression to yield results prior to the current month, but all working now.
    so code is
    Code:
    CBal: DSum("[Received]-[OutOthers]-[OutBookings]-[Outwork]","tblTestAccount","[CusCode]='" & [CusCode] & "' And [EntryDate]<#" & DateSerial(Year(Date()),Month(Date()),0) & "#")
    Trevor.

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

Similar Threads

  1. Totals Problem in a grouped report
    By dgmdvm in forum Reports
    Replies: 2
    Last Post: 06-01-2018, 05:18 PM
  2. Replies: 14
    Last Post: 09-19-2017, 02:51 PM
  3. Replies: 6
    Last Post: 09-14-2015, 06:05 PM
  4. Replies: 43
    Last Post: 08-06-2014, 08:21 PM
  5. DSum Syntax Help
    By desk4tbc in forum Programming
    Replies: 2
    Last Post: 07-01-2011, 02:31 PM

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