iif condition

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try it like this:

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")

Ed G
 
I'm afraid you lost me. It appears that you are trying to concatenate
multiple results from a single field, which should not work if your data is
atomic. In a fully normalized database system, the [Object_Code] field
should only have one value for any one record. Can you explain your
situation a little more?
 
Ed,

Thanks for your continued support.

The syntax you have suggested is not really what I wanted, because with it,
if object code is null, then purpose of travel becomes "check object code". I
would like "check object code" only to come up if a wrong code is entered in
the object code field and whenever object code is null, the purpose of travel
should be blank.

I hope this would help you better understand my problem.

Thanks

Alylia

Edward G said:
Try it like this:

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")

Ed G

Alylia said:
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...
 
Alylia,

I think you should consider going with the suggestion offered by Duane
Hookum. Make a table and call it
something like tblTravelCodes. Enter all your codes and their descriptions.
Add this table to your query and use the
description field instead of your IIf statement. Nulls will automatically
come up blank. An IIf statement in a separate
column of your query will satisfy your "wrong code" and you can concatenate
the two if you must output to a single field.
I am struggling with the Iif statement for wrong codes but will get back to
you unless of course someone else supplies one.

Ed



Alylia said:
Ed,

Thanks for your continued support.

The syntax you have suggested is not really what I wanted, because with it,
if object code is null, then purpose of travel becomes "check object code". I
would like "check object code" only to come up if a wrong code is entered in
the object code field and whenever object code is null, the purpose of travel
should be blank.

I hope this would help you better understand my problem.

Thanks

Alylia

Edward G said:
Try it like this:

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")

Ed G

Alylia said:
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
Click to expand...
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...
 
I have a field from a table named object code which is lookup. In my query I
have created another field named purpose of travel which should come up with
travel purpose based on the code in my object code field.

I am almost getting there with the syntax below, but for each travel
purpose, I am also getting "check object code" which is basically supposed to
be my elseif all conditions are not applicable. Interestingly, the is null
work fine. I need a bit of help with the object codes when they are valid and
invalid

Thanks

Al

Lynn Trapp said:
I'm afraid you lost me. It appears that you are trying to concatenate
multiple results from a single field, which should not work if your data is
atomic. In a fully normalized database system, the [Object_Code] field
should only have one value for any one record. Can you explain your
situation a little more?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Alylia said:
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...
 
First of all, I agree with Edward (and apparently Duane, although I don't
see a post from him in my news reader) that you should set up a separate
table for your object codes.

The reason you are get "check object code" with every code is that the last
IIF statement will always return a FALSE if there is a code in the field
and, since you are concatenating all of your results, it simply gets
attached on. If you insist on using an IIF statement to do this, then it
should be like this:

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")))))))))))))))

I'm not sure if Access will let you imbed IIF statements to that many
levels, but that is the correct syntax for what you are trying to do. Be
sure to verify the correct number of closing parens.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Alylia said:
I have a field from a table named object code which is lookup. In my query I
have created another field named purpose of travel which should come up with
travel purpose based on the code in my object code field.

I am almost getting there with the syntax below, but for each travel
purpose, I am also getting "check object code" which is basically supposed to
be my elseif all conditions are not applicable. Interestingly, the is null
work fine. I need a bit of help with the object codes when they are valid and
invalid

Thanks

Al

Lynn Trapp said:
I'm afraid you lost me. It appears that you are trying to concatenate
multiple results from a single field, which should not work if your data is
atomic. In a fully normalized database system, the [Object_Code] field
should only have one value for any one record. Can you explain your
situation a little more?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Alylia said:
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
Click to expand...
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...
 
Alylia,

Here's the thing. I don't know how to create a "double Iif statement".
If you take the advice below and create a table for your travel codes,
the only remaining problem is how to deal with wrong codes. What you
want to say in an expression is that if your code is not null but your
description is null, show "Check Object Code".
I don't know how to say this in a single IIf statement. So, I would create
separate IIf statements and then combine them.
The first would be: Wrong1: IIf([Description] Is Null,1,0)
The second would be: Wrong2: IIf([Object_Code] Is Not Null,1,0)
The third would be: Wrong3: IIf([Wrong1]+[Wrong2]=2,"Check Object Code")
The final would be: Combined: [Wrong3] & [Description]

Admittedly, this lacks elegance but it will work. Hopefully, someone will
explain how to double up an Iif statement.

Ed G
 
I have tried it and I am get back "the expression you have entered is too
complex".


Lynn Trapp said:
First of all, I agree with Edward (and apparently Duane, although I don't
see a post from him in my news reader) that you should set up a separate
table for your object codes.

The reason you are get "check object code" with every code is that the last
IIF statement will always return a FALSE if there is a code in the field
and, since you are concatenating all of your results, it simply gets
attached on. If you insist on using an IIF statement to do this, then it
should be like this:

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")))))))))))))))

I'm not sure if Access will let you imbed IIF statements to that many
levels, but that is the correct syntax for what you are trying to do. Be
sure to verify the correct number of closing parens.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Alylia said:
I have a field from a table named object code which is lookup. In my query I
have created another field named purpose of travel which should come up with
travel purpose based on the code in my object code field.

I am almost getting there with the syntax below, but for each travel
purpose, I am also getting "check object code" which is basically supposed to
be my elseif all conditions are not applicable. Interestingly, the is null
work fine. I need a bit of help with the object codes when they are valid and
invalid

Thanks

Al

Lynn Trapp said:
I'm afraid you lost me. It appears that you are trying to concatenate
multiple results from a single field, which should not work if your data is
atomic. In a fully normalized database system, the [Object_Code] field
should only have one value for any one record. Can you explain your
situation a little more?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


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...
 
I have tried it and I am get back "the expression you have entered is too
complex".
Click to expand...

That's what I was afraid of. You've run into the limit on the levels you can
nest IIF statements.
 
Damndest thing. It's not like I didn't try this exact same expression half a
dozen times unsuccessfully. But finally,
I got the blasted thing to work. If you build a table for your codes with
fieldnames
Object_Code and Description and add them to your query the following
expression can
account for your wrong codes: Wrong: IIf([Description] Is Null And
[Object_Code] Is Not Null,"check object code")
Then Expr: [Description] & [Wrong] concatenates the two into a single
column.
Hope that helps.


Ed G
 
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)
 
See my answer using the Switch function.

--
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)
 
Well, even though this wasn't my problem, I appreciate your pointing out the
Switch function which I agree is a neat function to
have at one's disposal. However, when I tried to run this on my system I got
an error message that the expression contained too many arguments.
When I clipped off the IsNull portion, I got an error message that the
expression was too complex to run. I am running Access 97 with 512 MB of
RDRAM. Did this expression actually work on your system?

Ed G

John Viescas said:
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)
Alylia said:
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...
 
John,

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

alylia

John Viescas said:
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)
Alylia said:
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...
 
Alylia,

Please try using a new table instead of trying to make this expression work.
If you build a table with fieldnames
Object_Code and Description and add the table and fields to your query.
The following expression (in a new column) can account for your wrong
codes:
Expr1: IIf([Description] Is Null And [Object_Code] Is Not Null,"Check Object
Code")
Then in another new column creating
Expr2: [Description] & [Expr1]
concatenates the two into a single column.
Hope that helps.

Ed G

Alylia said:
John,

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

alylia

John Viescas said:
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)
Alylia said:
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...
 
Ed-

Well, my original code is missing a closing paren after the IsNull, but
you're right. I can build the expression up to [Object_Code] = "8749",
"Board Trvl". If I try to add one more, it dies with "too complex." It's
OK with 616 characters in the expression, but can't handle more than that
for some strange reason. But I don't think it's the expression length,
because I tried shortening the field name and still get the error at the
same spot. It dies when I add the 15th pair of arguments ... hmmm. Nothing
in Help implies there's a limit to the number of arguments to Switch.

Alylia is going to have to put these codes in a table.

--
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)
Edward G said:
Well, even though this wasn't my problem, I appreciate your pointing out
the
Switch function which I agree is a neat function to
have at one's disposal. However, when I tried to run this on my system I
got
an error message that the expression contained too many arguments.
When I clipped off the IsNull portion, I got an error message that the
expression was too complex to run. I am running Access 97 with 512 MB of
RDRAM. Did this expression actually work on your system?

Ed G

John Viescas said:
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)
Alylia said:
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 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

John Viescas said:
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)
Alylia said:
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...
 
Thanks John. I don't often keep the Switch function on my radar screen,
although I should as it is an exact replica of Oracle's Decode statement --
the only way to do this in Oracle SQL.
 
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

John Viescas said:
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