We help IT Professionals succeed at work.

Dsum and iif on report

pdvsa
pdvsa asked
on
6 Comments
2 solutions
High Priority
40 Views
Last Modified: 2020-03-23
Experts,

I am trying to combine the below in an unbound field on a report.
Maybe its better to do it by VBA?  
Not sure which way is easier.

thank you...

In English:
If tblLetterOfCredit.PaidInFullYN = True then "Paid In Full", if not then Dsum but only if tblPayments.PaymentDate is not null (meaning there is a date entered)

--------------------------------------
If tblLetterOfCredit.PaidInFullYN = True then "Paid in Full"
Else:

If is not null [PaymentDate] then Dsum [Amount].  How to add Is Not Nulll to below?
=iif(DLookUp("[PaymentDate]","tblPayments","[LCID] = " & [LetterOfCreditID]
dsum("[Amount]","tblPayments","[LCID] = " & [LetterOfCreditID]
Comment
Watch Question

View Solutions Only

Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Perhaps something like
IIF(
	[PaidInFullYN] = True,
	"Paid In Full",
	IIF(
		IsNull([PaymentDate]) = True,
		"WhatToPutIfPaymentDateIsNull???",
		DSum("[Amount]", "tblPayments", "[LCID] = " & [LetterOfCreditID])
	)
)

Open in new window

pdvsaProject finance

Author

Commented:
Hi Daniel,

Thank you for your response.  Its pretty close.  
Could you kindly assist to add a Dlookup to reference tblPayments.[PaymentDate] since this field is not in my query and if I pulled tblPayments in then the grouping wouldn’t be how I need it.  I hope I have explained correctly.
<WhatToPutIfPaymentDateIsNull???
=> think 0 but will have to test (I dont want it to sum the [Amount] for that record).
this is how I have it now (only replaced WhatToPutIfPaymentDateIsNull with 0) :
=IIf([PaidInFullYN]=True,"Paid In Full",IIf(IsNull([PaymentDate])=True,0,DSum("[Amount]","tblPayments","[LCID] = " & [LetterOfCreditID])))

thank you
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Perhaps something like:
=IIf([PaidInFullYN]=True,"Paid In Full",IIf(IsNull(DLookUp("[PaymentDate]","tblPayments","[LCID] = " & [LetterOfCreditID]))=True,0,DSum("[Amount]","tblPayments","[LCID] = " & [LetterOfCreditID])))

Open in new window

President / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
You could also possibly add the tblPayments/PaymentDate to your recordsource to eliminate the extra DLookup.
pdvsaProject finance

Author

Commented:
Hi Daniel, looks good.  It works!  I thank you very much.  Have  good day...stay safe.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Any time and you as well!