Dsum error

[Tophan]

I have a query with the following fields

-SubContractor
-OrderNo
-OrderValue
-InvoiceNo
-Total

One OrderNo can have several invoices. I am trying to get a cumulative invoice total for each OrderNo

I've tried the following formula but it is returning the total for each invoice for each order number

SubT: DSum("[Total]","qryPaymentsToDate2","[OrderNo]='" & [OrderNo] & "'" And "[InvoiceNo]<=" & [InvoiceNo])

Where did I go wrong? If I limit the criteria to OrderNo only it is giving me the total of the invoices for that order...close but I really need the running total after each invoice.

Please help

 

[plog]

"[OrderNo]='" & [OrderNo] & "'" And "[InvoiceNo]<=" & [InvoiceNo]

You have an improperly formed string. The 'And' exists outside of the string, it needs to be inside double quoted area. You should only have 4 double quotes, not 6.

 

[Tophan]

View attachment 63186The queries to look at are

-qryPaymentsToDate
-qryPaymentsToDate2 - this is the one that has in the Dsum formula above

 

[Tophan]

Sorry...the query with the formula is just named Query1

 

[sneuberg]

Plog was right and I had to add tblInvoice, but I think this does it.

SubT: DSum("[Total]","qryPaymentsToDate2","[OrderNo]='" & [OrderNo] & "' And [tblInvoices].[InvoiceNo]<=" & [tblInvoices].[InvoiceNo])

\

in qryPaymentsToDate2

 

[Tophan]

plog - i don't understand which double quotation marks to remove; could you please show me?

 

[Tophan]

Thank you both! It works