asked on

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]

Daniel Pineault

Perhaps something like

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

Open in new window

pdvsa

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Daniel Pineault

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial