Query Help

[AGoodGuy]

I need help...I have fieldnames called Method, Auto/Manual and Payment Type from the table dbo_UWA_PaymentLog

I need a formula in my qeury to show the following:

(1)If Method = Epay and Auto/Manual = Auto then Payment Type = WEB PAY

(2)If Method = Epay and Auto/Manual = Manual then Payment Type = Remit Plus

(3)If Method = Credit Card and Auto/Manual = Auto then Payment Type = WEB PAY

(4)If Method = Epay and Auto/Manual = Auto then Payment Type = IVR

(%)If Method = Check and Auto/Manual = Manual then Payment Type = WEB PAY

 

[PHV]

(4) and (1) are contradictoty ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

In addition to PH's comments, consider creating a small table that can be used to find the Payment Type:
[tt][blue]
tblPaymentTypes
===================
Method AutoMan PmtType
-------- -------- ----------
Epay Auto Web Pay
Epay Manual Remit Plus
Credit Card Auto Web Pay
Check Manual Web Pay
[/blue][/tt]
Then you add tblPaymentTypes to your query and join the Method and AutoMan fields. PmtType will be calculated. This solution allows you to easily add more methods and payment types.

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Perhaps:

PaymentType: iif(Method="Epay",iif(AutoManual="Auto","WebPay","RemitPlus"),iif(Method="CreditCard",iif(AutoManual="Auto","WebPay","IVR"),"WebPay))

Randy

 

[dhookom]

Randy,
While your expression might be the simplest to build, it would be horrible to maintain over time [red]when[/red] different Methods are added or payment methods are redefined. I always assume these types of business rules will change over time.

Minimally, I would create a user-defined function and stick it into a module of business rules. This would keep the logic in one place for easier updating.

Nested IIf()s cause me to shudder ;-)

Duane
Hook'D on Access
MS Access MVP