Need Help Running A Sum in a Form

[GabbyB via AccessMonster.com]

I used your example on a running sum that I am working on. The problem I
have is that I'm trying to run a sum on my "previous" invoices only. The
problem I have, using your example:

Subtotal: DSum("[Payment]", "[YourTableName]", "[FleetNo] = " &

[FleetNo] & " AND PaymentDate <= #" & [PaymentDate] & "#")

is that my formula ends with AND InvDate < #"&[InvDate]&"#") because I only
want it to add up the records from previous invoices, not including the
current. However, the first record (which has no previous record) errors out.

I tried to put in an Iif(ISERROR statement, but that didn't work either. Any
suggestions?

Here is my query formula:
Prev: Format(DSum("[ThisPeriod]","[tblBillingDetails]","tblBillingDetails.
[CSDID] = " & [tblBillingDetails].[CSDID] & " AND [Date] < #" & [Date] & "#")

 

[strive4peace]

Hi Gabby,

wrap the dSum result with NZ (Null to zero) before you Format it in case
the result of dSum is Null

Format needs a value to work (Null means 'no value')

Format(Nz(dSum("Fieldname","Tablename","Conditions"),0),"FormatCode")

~~~

do not use DATE as a fieldname, it is a reserved word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html


Warm Regards,
Crystal

*
have an awesome day
*

I used your example on a running sum that I am working on. The problem I
have is that I'm trying to run a sum on my "previous" invoices only. The
problem I have, using your example:

Subtotal: DSum("[Payment]", "[YourTableName]", "[FleetNo] = " &

[FleetNo] & " AND PaymentDate <= #" & [PaymentDate] & "#")

is that my formula ends with AND InvDate < #"&[InvDate]&"#") because I only
want it to add up the records from previous invoices, not including the
current. However, the first record (which has no previous record) errors out.

I tried to put in an Iif(ISERROR statement, but that didn't work either. Any
suggestions?

Here is my query formula:
Prev: Format(DSum("[ThisPeriod]","[tblBillingDetails]","tblBillingDetails.
[CSDID] = " & [tblBillingDetails].[CSDID] & " AND [Date] < #" & [Date] & "#")