Simple "IF" Expression in Access Report

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

Hi,
I have an Access report I have been asked to ammend. I have information
in a table called Main which has a record called App_Type. App_Type
contains a 2 character code (e.g. DE, NW, UR). These codes have meaning
to us but mean very little to others - (e.g. DE = Deleted, NW = New, UR
= Urgent). I can call this information through to a report but it
displays as "UR" or "DE".
Our report is going to be read by external people so must be
simplified. I need an expression that changes the code and displays the
full text. I don't know how to write an expression in Access but I
imagine the logic would be something like this :-

: If App_Type = "UR" Then Display "Urgent"; If "NW" The Display
"New"...
The result in the report would be that the reader would only be able to
see the words "Urgent" and not the code.

Can anyone help
any help is appreciated

regards

Nick
 
Nick said:
Hi,
I have an Access report I have been asked to ammend. I have information
in a table called Main which has a record called App_Type. App_Type
contains a 2 character code (e.g. DE, NW, UR). These codes have meaning
to us but mean very little to others - (e.g. DE = Deleted, NW = New, UR
= Urgent). I can call this information through to a report but it
displays as "UR" or "DE".
Our report is going to be read by external people so must be
simplified. I need an expression that changes the code and displays the
full text. I don't know how to write an expression in Access but I
imagine the logic would be something like this :-

: If App_Type = "UR" Then Display "Urgent"; If "NW" The Display
"New"...
The result in the report would be that the reader would only be able to
see the words "Urgent" and not the code.
Click to expand...

Go to the report's query and make a calculated field:

Iif([App_Type] = "UR","Urgent",Iif([App_Type] = "NW","New",Iif([App_Type] =
"DE","Deleted")))

HTH - Keith.
www.keithwilby.com
 
Seems to me the IIf statement needs to cover only three possibilities:
Iif([App_Type] = "UR","Urgent",Iif([App_Type] = "NW","New","Deleted"))

Keith Wilby said:
Nick said:
Hi,
I have an Access report I have been asked to ammend. I have information
in a table called Main which has a record called App_Type. App_Type
contains a 2 character code (e.g. DE, NW, UR). These codes have meaning
to us but mean very little to others - (e.g. DE = Deleted, NW = New, UR
= Urgent). I can call this information through to a report but it
displays as "UR" or "DE".
Our report is going to be read by external people so must be
simplified. I need an expression that changes the code and displays the
full text. I don't know how to write an expression in Access but I
imagine the logic would be something like this :-

: If App_Type = "UR" Then Display "Urgent"; If "NW" The Display
"New"...
The result in the report would be that the reader would only be able to
see the words "Urgent" and not the code.
Click to expand...

Go to the report's query and make a calculated field:

Iif([App_Type] = "UR","Urgent",Iif([App_Type] = "NW","New",Iif([App_Type]
= "DE","Deleted")))

HTH - Keith.
www.keithwilby.com
Click to expand...
 
BruceM said:
Seems to me the IIf statement needs to cover only three possibilities:
Iif([App_Type] = "UR","Urgent",Iif([App_Type] = "NW","New","Deleted"))
Click to expand...

True enough, well caught!

Keith.
 
I wouldn't create an expression like this where a lookup table should be
used. Set the App_Type as the primary key of the new table and add an
App_Title field. I don't care to manage data in expressions.

--
Duane Hookom
MS Access MVP

Keith Wilby said:
BruceM said:
Seems to me the IIf statement needs to cover only three possibilities:
Iif([App_Type] = "UR","Urgent",Iif([App_Type] = "NW","New","Deleted"))
Click to expand...

True enough, well caught!

Keith.
Click to expand...
 
Thank you, problem solved.
I have also used the same type of Iif statement on other problem we
have been getting.

thanks for all your help

regards

Nick


Keith said:
Nick said:
Hi,
I have an Access report I have been asked to ammend. I have information
in a table called Main which has a record called App_Type. App_Type
contains a 2 character code (e.g. DE, NW, UR). These codes have meaning
to us but mean very little to others - (e.g. DE = Deleted, NW = New, UR
= Urgent). I can call this information through to a report but it
displays as "UR" or "DE".
Our report is going to be read by external people so must be
simplified. I need an expression that changes the code and displays the
full text. I don't know how to write an expression in Access but I
imagine the logic would be something like this :-

: If App_Type = "UR" Then Display "Urgent"; If "NW" The Display
"New"...
The result in the report would be that the reader would only be able to
see the words "Urgent" and not the code.
Click to expand...

Go to the report's query and make a calculated field:

Iif([App_Type] = "UR","Urgent",Iif([App_Type] = "NW","New",Iif([App_Type] =
"DE","Deleted")))

HTH - Keith.
www.keithwilby.com
Click to expand...
 
Duane Hookom said:
I wouldn't create an expression like this where a lookup table should be
used. Set the App_Type as the primary key of the new table and add an
App_Title field. I don't care to manage data in expressions.
Click to expand...

Point taken but I didn't see it as data management, merely substitution. I
don't see the harm in using an in-line IF in the case of just three
parameters.

Regards,
Keith.
 
I have seen too many applications where three became four then five,....

If you want to "hard-code" expressions, don't do it in a query or control
source. This should be done in a small user-defined-function. Then you can
use it anywhere and maintain it in one place.
 
Back
Top