Help with DSum #Error (1 Viewer)

[nj2la96]

Hi guys,

I am trying to do a simple query to get the running total from a field. I have ClientID, InvoiceDate, and InvoiceBalance fields and I am simply trying to get the running total of the InvoiceBalance field for each ClientID by InvoiceDate.

Now I've used the formula

ClientBalance: CCur(DSum("[InvoiceBalance]","Test","ClientID =" & [ClientID] & "AND InvoiceDate <=#" & [InvoiceDate] & "#"))

But this seems to work only on select fields and give me #Error on others. See attachment for a picture of my error.

What am I doing wrong??

 

[arnelgp]

you need extra space in "AND", should be " AND ". also try formatting your date to US format.

[InvoiceDate] < = #" & Format([InvoiceDate], "mm/dd/yyyy") & "#"

 

[nj2la96]

Thanks for your response.

I tried that but no luck, I still get #Error. I'm in UK so the format for me would be dd/mm/yyyy.

It's really weird that it works on certain fields and it doesn't on others. I've looked up the table and the data looks right to me

Any other ideas?

 

[arnelgp]

if your dsum does not find any record, it will return a Null value. therefore converting it to currency type (Ccur()) will result in error. try catching this error:

ClientBalance: NZ(CCur(DSum("[InvoiceBalance]","Test","ClientID =" & [ClientID] & "AND InvoiceDate <=#" & Format([InvoiceDate],"mm/dd/yyyy") & "#")) , 0)

 

[nj2la96]

Oh wow. Thanks a lot. It worked!!! You've someone very happy ty