asked on 3/30/2020

Dsum - add criteria for [PaymentDate]

Experts, how can I modify the below False part (the Dsum) to only sum if tblPayments.[PaymentDate] is not null (meaning the field has a date in it). The below is in the query design window. II am not sure if it matters but tblPayments is not a table in the query.

thank you

SumPayments: IIf([PaidInFullYN]=True,"Paid In Full",DSum("Amount","tblPayments","[LCID] = " & [LetterOfCreditID]))

Microsoft Access

Last Comment

John Tsioumpris

3/30/2020

Jim Dettman (EE MVE)

3/30/2020

SumPayments: IIf([PaidInFullYN]=True,"Paid In Full",NZ(DSum("Amount","tblPayments","[LCID] = " & [LetterOfCreditID]),0))

Jim.

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

3/30/2020

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

Gustav Brock

3/30/2020

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

pdvsa

3/30/2020

ASKER

Hi guys, thanks for the responses.

do you know why it works in the query design window but when I drag the field onto the report it gives me an error after running rpt of the

following. tblPayments is not in the record source if that matters. I do not get the error if I remove the: And [PaymentDate] Is Not Null"

Gustav Brock

3/30/2020

But why not leave it in the query? In most case the query is better to do the hard work than the report.
Also, remove any sorting in the source query if present. Move that to the report.

SOLUTION

John Tsioumpris

3/30/2020

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial