dsum in access query returns null

[Guest]

Hello! I'm trying to creating a running sum in an access query. I'm showing
the invoice value as one column (called Tot_Amt) and a running sum of that
bill value in a second column. I've tried to follow this the instructions in
this link...
http://support.microsoft.com/default.aspx?scid=kb;en-us;290136

It works when i do it in the Northwind database, but when I try to do it in
my database, I get a blank column. My formula is...

Running Total: Format(DSum("Tot_Amt","Customer Reconciliation
Current","[DT_PRCS]<=" & [DT_PRCS] & ""),"$0,000.00")

Does anyone have any idea what I'm doing wrong?
Thank you!
Megan

 

[Guest]

Is DT_PRCS numeric or text? Your syntax suggests numeric. If it is text, try:
Running Total: Format(DSum("Tot_Amt","Customer Reconciliation
Current","[DT_PRCS]<=""" & [DT_PRCS] & """"),"$0,000.00")

Also, I rarely if ever format values in queries. I try to do this within the
control properties on forms and reports.

 

[Guest]

Duane left out another alternative. If [DT_PRCS] is a date field, then try
it like:

Running Total: DSum("Tot_Amt","Customer Reconciliation
Current","[DT_PRCS]<=#" & [DT_PRCS] & "#")

HTH
Dale

 

[Guest]

Thank you! I got that part to work. However, now it's summing all the data
in the table, not just the one account number I'm querying. So I'm trying to
add another criteria to the query so it looks like this....

Running Total: Format(DSum("Tot_Amt","TMobile Reconciliation
Current","[Utility Account] = " & [Utility Account] & "And [DT_PRCS]<=#" &
[Process Date] & "#"),"$0,000.00")

but I get a weird error message that says...

You tried to execute a query that does not include the specified expression
Format(DSum("Tot_Amt","TMobile Reconciliation Current","[Utility Account] = "
& [Utility Account] & "And [DT_PRCS]<=#" & [Process Date] & "#"),"$0,000.00")
as part of an aggregate function.

Any ideas what that means?

Thank you!
Megan

Duane left out another alternative. If [DT_PRCS] is a date field, then try
it like:

Running Total: DSum("Tot_Amt","Customer Reconciliation
Current","[DT_PRCS]<=#" & [DT_PRCS] & "#")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

Hello! I'm trying to creating a running sum in an access query. I'm showing
the invoice value as one column (called Tot_Amt) and a running sum of that
bill value in a second column. I've tried to follow this the instructions in
this link...
http://support.microsoft.com/default.aspx?scid=kb;en-us;290136

It works when i do it in the Northwind database, but when I try to do it in
my database, I get a blank column. My formula is...

Running Total: Format(DSum("Tot_Amt","Customer Reconciliation
Current","[DT_PRCS]<=" & [DT_PRCS] & ""),"$0,000.00")

Does anyone have any idea what I'm doing wrong?
Thank you!
Megan

 

[Guest]

I think I figured it out!!!! Thank you so much for your help. The formula I
ended up using was...

Running Total: DSum("Tot_Amt","Account Reconciliation Current","[Account
Reconciliation Current].[Utility Account] = """ & [Account Reconciliation
Current].[Utility Account] & """ And [DT_PRCS]<=#" & [Process Date] & "#")

I think my problems that you solved were using the # signs to turn the date
into a date and using the extra " signs to put the Utility Account criteria
in.