?
Solved

Syntax Problem with DSum in Access Query

Posted on 2009-12-22
17
Medium Priority
?
366 Views
Last Modified: 2012-05-08
I have a Dsum statement which will not run correctly:
RunBal: DSum("Amt$","Details","[Posting Date]<=" & [Posting Date] & " AND [AdvID]=" & [AdvID] & "")

The data looks like this:
AdvID      Posting Date      Amt$
1530-77620-29160      8/21/2007      2000
1530-77620-29160      9/25/2007      -991.18
1530-77620-29160      10/19/2007      -997.25
1530-81093-16840      3/5/2009      500
1530-90106-2WK      5/29/2007      2695
1530-90106-2WK      6/7/2007      21330
1530-90106-2WK      6/10/2007      -2694.85

The desired result is the running balance for each AdvID, per the date of the transaction.  When the AdvID changes, reset running total to zero and start over .  The current query output is either blank or #Error.
John
0
Comment
Question by:mtwservice
  • 6
  • 5
  • 4
  • +2
17 Comments
 
LVL 85
ID: 26107207
Does [Posting Date] or [AdvID] contain Null values? If so, you may have issues with this.

YOu may also try adding the Date delimiters:

RunBal: DSum("Amt$","Details","[Posting Date]<=#" & [Posting Date] & "# AND [AdvID]=" & [AdvID] & "")
0
 
LVL 39

Expert Comment

by:thenelson
ID: 26107220
Try:
RunBal: DSum("Amt$","Details","[Posting Date]<=#" & [Posting Date] & "# AND [AdvID]=" & [AdvID])


VBA data type-declaration characters:
Number (base 10): No type-declaration character needed (the number will automatically be converted to the type needed)
example: 1234, 67.77

REQUIRED data type-declaration characters:
String: Bracket with quotation marks or apostrophes (REQUIRED)
example: "This is a string" or 'this is a string'

date/time: Bracket with pound signs (also called number signs) (REQUIRED)
example: #6/1/1947#, #June 17, 1999#, #3:30Pm#, #1/5/63 2:33#

Hexadecimal number: Precede with &h
example: &H10

0ctal number: Precede with &o
example: &O10

optional, explicitly specified data type-declaration characters:
Integer: Append with percent sign
example: 1234%

Long: Append with ampersand  
example: 67877768&

Currency: Append with at sign
example: 62.45@

Single: Append with exclamation point  
example: 123.777!, 67.00!

Double: Append with number sign
example: 677767.8888#
0
 

Author Comment

by:mtwservice
ID: 26107771
Does [Posting Date] or [AdvID] contain Null values? If so, you may have issues with this.
           I don't have any NULL values
YOu may also try adding the Date delimiters:
            Great suggestion.  I tried it but it still gives the same result.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:mtwservice
ID: 26107787
Try:
RunBal: DSum("Amt$","Details","[Posting Date]<=#" & [Posting Date] & "# AND [AdvID]=" & [AdvID])

I did try this and it made no difference.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 26107861
Ok, let's check basics: Is the Details table or query spelled exactly the same? - Copy and paste the name from the main database window into the DSum routine. Does the Details table have the fields Amt$, [Posting Date] and AdvID and are they spelled exactly the same? Does the query this is in have the fields [Posting Date] and AdvID and are they spelled exactly the same?  If you have a column each for [Posting Date] and AdvID in the query, do they display correctly?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 26107903
Is this being run from a form and do the  two textboxes on the form bear the same name as the control sources ie. [Posting Date] and [AdvID]?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 26107921
IOW:

RunBal: DSum("Amt$","Details","[Posting Date]<=#" & [Posting Date] & "# AND [AdvID]=" & [AdvID])
----------------------------------------------field--------------------------??------------------------field-----------??

Where does the info for the two inputs marked ?? come from??
0
 

Author Comment

by:mtwservice
ID: 26107951
Ok, let's check basics: Is the Details table or query spelled exactly the same?  - Yes

- Copy and paste the name from the main database window into the DSum routine. OK
Does the Details table have the fields Amt$, [Posting Date] and AdvID and are they spelled exactly the same?  - Yes
Does the query this is in have the fields [Posting Date] and AdvID and are they spelled exactly the same?  - Yes, these fields show in the result of running the query.
If you have a column each for [Posting Date] and AdvID in the query, do they display correctly?  Yes, and they show.  It's only the "Dsum" output field that has the blank or #Error results.
 

Dsum-output.jpg
0
 
LVL 44

Expert Comment

by:GRayL
ID: 26107967
Did you see my comments?
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 26107992
>>>>>The desired result is the running balance for each AdvID, per the date of the transaction.  When the AdvID changes, reset running total to zero and start over . <<<<<<<

Why not just add Totals to your query???  Group on AdvID, Posting Date and Sum Amt$.

ET
0
 
LVL 39

Accepted Solution

by:
thenelson earned 500 total points
ID: 26107995
I see that AdvID is text - not a number so try:
RunBal: DSum("Amt$","Details","[Posting Date]<=#" & [Posting Date] & "# AND [AdvID]='" & [AdvID] & "'")

expanded for clarity (note the apostrophes):
RunBal: DSum("Amt$","Details","[Posting Date]<=#" & [Posting Date] & "# AND [AdvID]='  " & [AdvID] & "  '  ")
0
 
LVL 44

Expert Comment

by:GRayL
ID: 26108003
OK, I see what you are doing now.  Disregard my comments.
0
 

Author Comment

by:mtwservice
ID: 26108007
Attaching Details Data Table screenshot
Details-data-table.jpg
0
 

Author Comment

by:mtwservice
ID: 26108015
Attaching Running Bal Query screenshot
Run-Bal-Query.jpg
0
 

Author Closing Comment

by:mtwservice
ID: 31669111
Thanks!!
0
 
LVL 39

Expert Comment

by:thenelson
ID: 26108041
Post the SQL of the query Goto View, SQL view and post that.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 26108056
Reread my comment http:#a26107220 explaining VBA data type-declaration characters.

You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy computing!

Nelson
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question