Dsum and iif on report

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]

Microsoft AccessVBA

Last Comment

Daniel Pineault

8/22/2022 - Mon

Daniel Pineault

3/22/2020

Perhaps something like

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

Open in new window

pdvsa

3/22/2020

ASKER

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

SOLUTION

Daniel Pineault

3/23/2020

THIS SOLUTION 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

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

ASKER CERTIFIED SOLUTION