iif condition

  • Thread starter Thread starter Guest
  • Start date Start date
You are going to replace your current Purpose_Of_Travel expression with the
one John supplied,
except I strongly suspect it will not work as you desire. I would recommend
trying the instructions
in my post if it doesn't.

Ed G


Alylia said:
We are sure getting there. But what I do not understand is where do I put the
syntax below:

Purpose_Of_Travel: IIf(IsNull(MyTable.[CodeDesc]), "",
IIf(IsNull([Code_Desc]), "Check Object Code", [Code_Desc]))

Since in the query, I have also the field Purpose_of_Travel which
automatically comes up as an object code is entered. I am thinking that the
below syntax should help handle the error that comes up if no code or an
invalid code is entered.

Thanks for your continued support

Alylia

John Viescas said:
See my reply to Edward - it dies when you try to add the 15th pair of
arguments (Retirement Trvl). You'll need to build a lookup table to handle
this. First column should be Object_Code Text (4) and second column should
be Code_Desc Text(30). Make the first column the primary key. Entries in
the table should match your list:

2145 Sick Trvl
4175 Maternity Trvl
2101 Educational Trvl

... and so on.

Add this table to your query and create a Join line from Object_Code in your
original table to this new one if Access doesn't do it automatically.
Double-click the join line and choose the option to select all records from
your original table and any matching ones from the new lookup table. Remove
your original expression and add this:
Purpose_Of_Travel: IIf(IsNull(MyTable.[CodeDesc]), "",
IIf(IsNull([Code_Desc]), "Check Object Code", [Code_Desc]))

If you think about it, this is really more flexible. You can add / change /
delete codes in the table in the future without having to mess with your
query.


--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Alylia said:
John,

I have tried your suggested syntax and i am getting the msg "the
expression
you have entered is too complex"

alylia

:

Use the Switch function.

Purpose_of_Travel: Switch([Object_Code] = "2145", "Sick Trvl",
[Object_Code]
= "4175", "Maternity Trvl", [Object_Code] = "2101", "Educational Trvl",
[Object_Code] = "9874", "Retirement Trvl", [Object_Code] = "0148",
"Compensationate Trvl", [Object_Code] = "4512", "Conference Trvl",
[Object_Code] = "6145", "Training Trvl", [Object_Code] = "0409", "Annual
Leave Trvl", [Object_Code] = "4152", "Transfer Trvl", [Object_Code] =
"7485", "Marketing Trvl", [Object_Code] = "4155", "Community Trvl",
[Object_Code] = "2478", "Emergency Trvl", [Object_Code] = "8459",
"Management Trvl", [Object_Code] = "8749", "Board Trvl", [Object_Code] =
"3652", "Retirement Trvl", IsNull([Object_Code], "", True, "Check Object
Code")

If the object code is valid, the function returns the related string. If
the object code is Null, the function will return an empty string. If
none
of the above, the function returns "Check Object Code".

Neat, huh?
--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello,
My problem has got to do with the Purpose of
Travel field (query created field). Along with for e.g. object code -
2145,
purpose of travel is "Sick TrvlCheck Object Code", instead of just
"Sick
Travel". Simply it means that each of the conditions (with the
exception
of
the null condition) selected comes up with output of the else condition

Please review the syntax below and advice:

Purpose_of_Travel: IIF([Object_Code] = "2145","Sick Trvl") &
IIF([Object_Code]="4175","Maternity Trvl") &
IIF([Object_Code]="2101","Educational Trvl") &
IIF([Object_Code]="9874","Retirement Trvl") & IIF([Object_Code]
="0148","Compensationate Trvl") & IIF([Object_Code]="4512","Conference
Trvl")
& IIF([Object_Code]="6145","Training Trvl") &
IIF([Object_Code]="0409","
Annual Leave Trvl") & IIF([Object_Code]="4152","Transfer Trvl") &
IIF([Object_Code]="7485","Marketing Trvl") & IIF([Object_Code]=
"4155","Community Trvl") & IIF([Object_Code]="2478","Emergency Trvl") &
IIF([Object_Code]= "8459","Management Trvl") &
IIF([Object_Code]="8749","Board Trvl") &
IIF([Object_Code]="3652","Retirement
Trvl") & IIF(IsNull([Object_Code])," ","Check Object Code")

Alylia
Click to expand...
Click to expand...
Click to expand...
 
See Ed's post. It would help if you posted the SQL for your query.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Alylia said:
We are sure getting there. But what I do not understand is where do I put
the
syntax below:

Purpose_Of_Travel: IIf(IsNull(MyTable.[CodeDesc]), "",
IIf(IsNull([Code_Desc]), "Check Object Code", [Code_Desc]))

Since in the query, I have also the field Purpose_of_Travel which
automatically comes up as an object code is entered. I am thinking that
the
below syntax should help handle the error that comes up if no code or an
invalid code is entered.

Thanks for your continued support

Alylia

John Viescas said:
See my reply to Edward - it dies when you try to add the 15th pair of
arguments (Retirement Trvl). You'll need to build a lookup table to
handle
this. First column should be Object_Code Text (4) and second column
should
be Code_Desc Text(30). Make the first column the primary key. Entries
in
the table should match your list:

2145 Sick Trvl
4175 Maternity Trvl
2101 Educational Trvl

... and so on.

Add this table to your query and create a Join line from Object_Code in
your
original table to this new one if Access doesn't do it automatically.
Double-click the join line and choose the option to select all records
from
your original table and any matching ones from the new lookup table.
Remove
your original expression and add this:
Purpose_Of_Travel: IIf(IsNull(MyTable.[CodeDesc]), "",
IIf(IsNull([Code_Desc]), "Check Object Code", [Code_Desc]))

If you think about it, this is really more flexible. You can add /
change /
delete codes in the table in the future without having to mess with your
query.


--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Alylia said:
John,

I have tried your suggested syntax and i am getting the msg "the
expression
you have entered is too complex"

alylia

:

Use the Switch function.

Purpose_of_Travel: Switch([Object_Code] = "2145", "Sick Trvl",
[Object_Code]
= "4175", "Maternity Trvl", [Object_Code] = "2101", "Educational
Trvl",
[Object_Code] = "9874", "Retirement Trvl", [Object_Code] = "0148",
"Compensationate Trvl", [Object_Code] = "4512", "Conference Trvl",
[Object_Code] = "6145", "Training Trvl", [Object_Code] = "0409",
"Annual
Leave Trvl", [Object_Code] = "4152", "Transfer Trvl", [Object_Code] =
"7485", "Marketing Trvl", [Object_Code] = "4155", "Community Trvl",
[Object_Code] = "2478", "Emergency Trvl", [Object_Code] = "8459",
"Management Trvl", [Object_Code] = "8749", "Board Trvl", [Object_Code]
=
"3652", "Retirement Trvl", IsNull([Object_Code], "", True, "Check
Object
Code")

If the object code is valid, the function returns the related string.
If
the object code is Null, the function will return an empty string. If
none
of the above, the function returns "Check Object Code".

Neat, huh?
--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello,
My problem has got to do with the Purpose of
Travel field (query created field). Along with for e.g. object
code -
2145,
purpose of travel is "Sick TrvlCheck Object Code", instead of just
"Sick
Travel". Simply it means that each of the conditions (with the
exception
of
the null condition) selected comes up with output of the else
condition

Please review the syntax below and advice:

Purpose_of_Travel: IIF([Object_Code] = "2145","Sick Trvl") &
IIF([Object_Code]="4175","Maternity Trvl") &
IIF([Object_Code]="2101","Educational Trvl") &
IIF([Object_Code]="9874","Retirement Trvl") & IIF([Object_Code]
="0148","Compensationate Trvl") &
IIF([Object_Code]="4512","Conference
Trvl")
& IIF([Object_Code]="6145","Training Trvl") &
IIF([Object_Code]="0409","
Annual Leave Trvl") & IIF([Object_Code]="4152","Transfer Trvl") &
IIF([Object_Code]="7485","Marketing Trvl") & IIF([Object_Code]=
"4155","Community Trvl") & IIF([Object_Code]="2478","Emergency
Trvl") &
IIF([Object_Code]= "8459","Management Trvl") &
IIF([Object_Code]="8749","Board Trvl") &
IIF([Object_Code]="3652","Retirement
Trvl") & IIF(IsNull([Object_Code])," ","Check Object Code")

Alylia
Click to expand...
Click to expand...
Click to expand...
 
Back
Top