DSum with Date (Access 2000,2002)

Topic

New Reply

The below code is a Field in a saved Query that I am attempting to use to populate a subForm. The subForm and main Form are editable. The first bit of code works fine except that it returns the total sum for the SOInvoiceNumber not those prier to the current RemitDate, I need to filter this a bit, by the previous RemitDate if any and if none exist the return of a 0 (zero). The RemitDate actually is in the tblCustomerRemits table.

PrevPaid:Format(DSum(“RemitAmount”,”tblCustomerRemitsDetail”,”SOInvoiceNumber=” & [SOInvoiceNumber]),”$#,##0.00″)

I tried using the following code this returns a sum of all of the records.
PrevPaid:Format(DSum(“RemitAmount”,”tblCustomerRemitsDetail”,”SOInvoiceNumber=” & [SOInvoiceNumber] And “RemitDate<" & [RemitDate]),"$#,##0.00")

The working method of the subForm is, once a SOInvoiceNumber has been selected or entered the other information needed to process a Customers Remittance, most of this is displayed only. What I would like to have happen is that, when at anytime that the record is viewed after it has been entered that it shows the same value as when it was entered, I would rather not store this value.

Reply | Quote

Replies

The syntax of your where-condition is not correct: the And should be within the quotes, and date values should be surrounded by # characters. Also, if there is a chance that you have users with a non-US date format, you must convert the date to mm/dd/yyyy format since that is the only format SQL understands. Try

PrevPaid:Format(DSum(“RemitAmount”,”tblCustomerRemitsDetail”,”SOInvoiceNumber=” & [SOInvoiceNumber] & ” And RemitDate<#" & Format([RemitDate], "mm/dd/yyyy") & "#"),"$#,##0.00")

Reply | Quote

Thank you kindly, knew I was missing something, just could not see what.

Reply | Quote