DSum with Date

[ThreeCrow]

Is there a way to add to the below code (a Field in a saved Query) that if the RemitDate is the same as the RemitDate and SOInvoiceNumber is the same the SOInvoiceNumber, that the DSum function will work. And also still work as the code reads.


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


I have attached a view of what I’m trying to accomplish, I apologize for the crudeness of the Form it’s still under construction.

 

[RV]

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

RV

 

[ThreeCrow]

Thank you kindly RV, your reply is not incorrect, it solves half of the problem.

I should explain in a little more detail


The brain twist is, If the Invoice Number has a previous payment before this remit date that value is returned, if the Invoice Number does not have a previous payment before this remit date then a zero value is returned.

Then the really twisted part, if the Invoice Number does not have a previous payment before this remit date but does have a previous payment for this remit date, I would like this value returned.

When we receive payment (remittance) checks, one Invoice could have many lines as part of the actual Invoice payment. And there could be previous payment on an earlier check.

I think I'm going to have to use another method.

 

[SamDeMan]

format doesn't return a date (it returns a varient, see help). you need to use something like cdate(format([RemitDate....

sam

 

[RV]

format doesn't return a date (it returns a varient, see help). you need to use something like cdate(format([RemitDate....

sam

That's not what the problem is all 'bout, sam.

RV

 

[SamDeMan]

That's not what the problem is all 'bout, sam.

RV

sorry bro,

sam

 

[ThreeCrow]

no need to apologies

thank you kindly for your in put

 

[ThreeCrow]

Got it solved

PrevPaidCalc:Format(Nz(DSum("RemitAmount","tblCust omerRemitsDetail","SOInvoiceNumber=" & [SOInvoiceNumber] & " And RemitDate<#" & Format([RemitDate],"mm/dd/yyyy") & "#"),0),"$#,##0.00")

The above should be

PrevPaidCalc:Format(Nz(DSum("RemitAmount","tblCust omerRemitsDetail","SOInvoiceNumber=" & [SOInvoiceNumber] & " And RemitDetailID<#" & [RemitDetailID]),0),"$#,##0.00")


The expression now does what I needed it to do. Which is create a Running Sum Invoice Payments.

Hope this can help someone else.