Access Formula

[CCPKGUY]

I have a SQL query in Access as follows:

SELECT pu_balance_hist.account_no, pu_balance_hist.occupant_code, pu_balance_hist.trans_date, pu_balance_hist.jour_code, pu_balance_hist.trans_amt, pu_balance_hist.desc_code
FROM Northstar_live.harris_live.pu_balance_hist pu_balance_hist
WHERE (pu_balance_hist.trans_date>={ts '2013-01-01 00:00:00'}) AND (pu_balance_hist.jour_code='CR')
ORDER BY pu_balance_hist.trans_date

I need to include a column in Access that will give me the following information to include in the above SQL statement:

If pu_balance_hist.desc_code = "140","Cash"
= "141","Check"
= "1", "Money Order Payment"
= "50","Cross Applied Payment"
= "75","Wire Transfer Payment"
= "142","Debit"
= "144',"Web Credit Card Payment"
= "160", "Return Check"
= "161", "NSF Check"
= "162", "NSF Check Charge"
= "164", "Balance Transfer

 

[sxschech]

How about this:

[tt]
IIf([pu_balance_hist].[desc_code]="140","Cash",IIf([pu_balance_hist].[desc_code]="141","Check",IIf([pu_balance_hist].[desc_code]="1","Money Order Payment",IIf([pu_balance_hist].[desc_code]="50","Cross Applied Payment",IIf([pu_balance_hist].[desc_code]="75","Wire Transfer Payment",IIf([pu_balance_hist].[desc_code]="142","Debit",IIf([pu_balance_hist].[desc_code]="144","Web Credit Card Payment",IIf([pu_balance_hist].[desc_code]="160","Return Check",IIf([pu_balance_hist].[desc_code]="161","NSF Check",IIf([pu_balance_hist].[desc_code]="162","NSF Check Charge",IIf([pu_balance_hist].[desc_code]="164","Balance Transfer"))))))))))) AS Balance_Desc
[/tt]

 

[dhookom]

IMO, don't use nested IIf()s since any change in desc_code requires updating the expression.

There should be a small lookup table of desc_code with a field that stores "cash" or "Check" or ... If that is impossible, then create a small user-defined function with a Select Case statement to find and choose the appropriate value based on desc_code.

Duane
Hook'D on Access
MS Access MVP

 

[CCPKGUY]

Maybe I could do a VLOOKUP. I have a column named Decsription Code but would like to a VLOOKUP with the following information. Can you assist me with that?

141 Check
1 Money Order Payment
50 Cross Applied Payment
75 Wire Transfer Payment
142 Debit
144 Web Credit Card Payment
160 Return Check
161 NSF Check
162 NSF Check Charge
164 Balance Transfer

 

[dhookom]

Why not use my suggestion? VLookup is Excel not Access. DLookup might work but poorly.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

I have a column named Decsription Code
[tt]
141 Check
1 Money Order Payment
50 Cross Applied Payment
75 Wire Transfer Payment
142 Debit
144 Web Credit Card Payment
160 Return Check
161 NSF Check
162 NSF Check Charge
164 Balance Transfer
[/tt]

Is that what you have in your Decsription Code column?

Why not establish another little table (like dhookom suggested):[tt]

SomeTableName[/tt][pre]
ID(PK) DESC
1 Money Order Payment
50 Cross Applied Payment
75 Wire Transfer Payment
141 Check
142 Debit
144 Web Credit Card Payment
160 Return Check
161 NSF Check
162 NSF Check Charge
164 Balance Transfer
[/pre]

and use it as any other ('look-up') table?

Have fun.

---- Andy