Syntax Problem with DSum in Access Query

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
mtwserviceAsked:
Who is Participating?
 
thenelsonConnect With a Mentor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
thenelsonCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
mtwserviceAuthor Commented:
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
 
mtwserviceAuthor Commented:
Try:
RunBal: DSum("Amt$","Details","[Posting Date]<=#" & [Posting Date] & "# AND [AdvID]=" & [AdvID])

I did try this and it made no difference.
0
 
thenelsonCommented:
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
 
GRayLCommented:
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
 
GRayLCommented:
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
 
mtwserviceAuthor Commented:
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
 
GRayLCommented:
Did you see my comments?
0
 
Eric ShermanAccountant/DeveloperCommented:
>>>>>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
 
GRayLCommented:
OK, I see what you are doing now.  Disregard my comments.
0
 
mtwserviceAuthor Commented:
Attaching Details Data Table screenshot
Details-data-table.jpg
0
 
mtwserviceAuthor Commented:
Attaching Running Bal Query screenshot
Run-Bal-Query.jpg
0
 
mtwserviceAuthor Commented:
Thanks!!
0
 
thenelsonCommented:
Post the SQL of the query Goto View, SQL view and post that.
0
 
thenelsonCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.