Access 2033 query using IF THEN ELSE or IIF

[Dausa67]

Hello,
I have a report that I built in Crystal Reports 11. I have been asked to RE-BUILD as a query in Access 2003 for another user that uses Access 2003.

In the query there is a field that is called "STATUS" which consists of numbers or letters that most users do not know the meaning of. In my Crystal Report I created an IF THEN ELSE statement to clarify the meaning of the status numbers.

My Crystal Report code is:

if {ASI_SARO_REM.STATUS} = '1' then "Invoice" else 
if {ASI_SARO_REM.STATUS} = '2' then "Charges" else 
if {ASI_SARO_REM.STATUS} = '3' then "app1" else 
if {ASI_SARO_REM.STATUS} = '4' then "app2" else 
if {ASI_SARO_REM.STATUS} = '5' then "app3" else 
if {ASI_SARO_REM.STATUS} = '6' then "Remit" else 
if {ASI_SARO_REM.STATUS} = '7' then "Complete" else 
if {ASI_SARO_REM.STATUS} = 'A' then "Accounting Hold" else 
if {ASI_SARO_REM.STATUS} = 'Z' then "Sleeping" else
if {ASI_SARO_REM.STATUS} = "  " then "In Inspection"

In the Access 2003 query I am using a IIF expression. I can successfully run the query as long as I only use one IIF expression.
My current expression is:

STATUS: IIf([STATUS]='1',"Invoice",[STATUS])

QUESTIONS
[ul]
[li]Can I combine 10 IIF statements in one expression?[/li]
[li]If yes, How?[/li]
[li]If no, is there another way to do this?[/li]
[/ul]

Any and all help is greatly appreciated.


Clay

 

[Andrzejek]

I would go with the easy way: create another table [tt]tblStatus:[/tt]

[pre]
ID Description
1 Invoice
2 Charges
3 app1
4 app2
5 app3
6 Remit
7 Complete
A Accounting Hold
Z Sleeping" else
X In Inspection
[/pre]

and use this table to display what-is-what to the user.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

ditto on table!!!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Dausa67]

I followed what you said and I am able to make it work except for the last one
"X In Inspection".

How do I address this when the value is null?
Do I somehow change Null value to "X"?



Clay

 

[Andrzejek]

I wouldn't allow ASI_SARO_REM.STATUS to be NULL (because nothing equals to Null, not even a Null ) I'm not crazy about your A and Z as Status, either. I would have them all as Numbers. But...

You can either update all Nulls to X (or whatever you choose as a Letter/Number) or you can use NZ() function in Access NZ(ASI_SARO_REM.STATUS, 'X')

Thanks Skip, great minds think alike

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PHV]

Have a look to th Nz function.

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

 

[Dausa67]

That is what I needed. Thank you for your help.

Clay

 

[Andrzejek]

I am glad it worked for you

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.