Using iif condition

[Guest]

I am working on a query that has a created column based on one column with
codes in a lookup list from a table in the database. This new column named
Purpose_of_Travel should come up with travel purpose based on some codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull(

)," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study Travel","
")))

Problem - the null condition is working quite well, but if I select a
particular code from the drop down list or if I enter a wrong code, I am
getting #Error.

Can someone help please?

 

[Guest]

Hi Alylia,
I would like to suggest a better way to do a lookup. You can create a
lookup table called "Travel_Purpose" or anything you like. The table
definition is as

Table Travel_Purpose:
Code - Text
Description - Text

Then you can put all the code and description in that table.
Table Travel_Purpose:
Code Description
====== =============================
0140 Sick Travel
1452 Conference Travel
1002 Study Travel
..
..
..
etc

Nice thing about to do it this way is that you can add more code if needed
to be.
Then you can join the table based on the "Code" and get your description.

Hope this helps.

 

[Edward G]

Looks like a syntax problem. Try this:


Purpose_of_Travel: IIf(IsNull(

)," ") & IIf([Code]="0140","Sick
Travel") & IIf([Code]="1452","Conference Travel") &
IIf([Code]="1002","Study Travel")

Ed G

 

[Guest]

Thanks Ed G,

You've made my day

Alylia

Looks like a syntax problem. Try this:


Purpose_of_Travel: IIf(IsNull(

)," ") & IIf([Code]="0140","Sick
Travel") & IIf([Code]="1452","Conference Travel") &
IIf([Code]="1002","Study Travel")

Ed G





[QUOTE="Alylia"]
I am working on a query that has a created column based on one column with
codes in a lookup list from a table in the database. This new column named
Purpose_of_Travel should come up with travel purpose based on some codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull([Code])," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study Travel","
")))

Problem - the null condition is working quite well, but if I select a
particular code from the drop down list or if I enter a wrong code, I am
getting #Error.

Can someone help please?[/QUOTE]
[/QUOTE]

 

[Guest]

Ed G

I have encountered another problem with the syntax below. The code 6145 in
respect of Training Trvl is being highlighted with the error msg "The
expression you entered contains invalid syntax - You may have entered an
operand without an operator "

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

Pls assist

Thanks Ed G,

You've made my day

Alylia

Looks like a syntax problem. Try this:


Purpose_of_Travel: IIf(IsNull(

)," ") & IIf([Code]="0140","Sick
Travel") & IIf([Code]="1452","Conference Travel") &
IIf([Code]="1002","Study Travel")

Ed G





[QUOTE="Alylia"]
I am working on a query that has a created column based on one column with
codes in a lookup list from a table in the database. This new column named
Purpose_of_Travel should come up with travel purpose based on some codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull([Code])," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study Travel","
")))

Problem - the null condition is working quite well, but if I select a
particular code from the drop down list or if I enter a wrong code, I am
getting #Error.

Can someone help please?[/QUOTE]
[/QUOTE][/QUOTE]

 

[Edward G]

The only problem I can see is here: IIF([Object_Code]="4512","Conference
Trvl)
You need close quotes after Conference Trvl like this:
IIF([Object_Code]="4512","Conference Trvl")

Hope that does it.
Ed G

Ed G

I have encountered another problem with the syntax below. The code 6145 in
respect of Training Trvl is being highlighted with the error msg "The
expression you entered contains invalid syntax - You may have entered an
operand without an operator "

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

Pls assist

Thanks Ed G,

You've made my day

Alylia

Looks like a syntax problem. Try this:


Purpose_of_Travel: IIf(IsNull(

)," ") & IIf([Code]="0140","Sick
Travel") & IIf([Code]="1452","Conference Travel") &
IIf([Code]="1002","Study Travel")

Ed G





I am working on a query that has a created column based on one column with
codes in a lookup list from a table in the database. This new column named
Purpose_of_Travel should come up with travel purpose based on some codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull([Code])," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study
Travel","
")))

Problem - the null condition is working quite well, but if I select a
particular code from the drop down list or if I enter a wrong code, I am
getting #Error.

Can someone help please?
[/QUOTE][/QUOTE][/QUOTE]

 

[Duane Hookom]

Why would you want to maintain an expression like this when you could use
JL's solution to maintain a table? I would convert this to a data-driven
solution about the 2nd IIF().

--
Duane Hookom
MS Access MVP
--

Ed G

I have encountered another problem with the syntax below. The code 6145 in
respect of Training Trvl is being highlighted with the error msg "The
expression you entered contains invalid syntax - You may have entered an
operand without an operator "

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

Pls assist

Thanks Ed G,

You've made my day

Alylia

Looks like a syntax problem. Try this:


Purpose_of_Travel: IIf(IsNull(

)," ") & IIf([Code]="0140","Sick
Travel") & IIf([Code]="1452","Conference Travel") &
IIf([Code]="1002","Study Travel")

Ed G





I am working on a query that has a created column based on one column
with
codes in a lookup list from a table in the database. This new column
named
Purpose_of_Travel should come up with travel purpose based on some
codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull([Code])," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study
Travel","
")))

Problem - the null condition is working quite well, but if I select a
particular code from the drop down list or if I enter a wrong code, I
am
getting #Error.

Can someone help please?
[/QUOTE][/QUOTE][/QUOTE]

 

[Guest]

The problem was so small that I couldn't identify it. Thanks Ed G

Now the only problem I am encountering now is that with the each purpose of
travel, I get also "Check Object Code" i.e. the ELSE condition.

Example if I select object code - 2145, purpose of travel is Sick TrvlCheck
Object Code.

Please advice on the syntax below:

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

The only problem I can see is here: IIF([Object_Code]="4512","Conference
Trvl)
You need close quotes after Conference Trvl like this:
IIF([Object_Code]="4512","Conference Trvl")

Hope that does it.
Ed G

Ed G

I have encountered another problem with the syntax below. The code 6145 in
respect of Training Trvl is being highlighted with the error msg "The
expression you entered contains invalid syntax - You may have entered an
operand without an operator "

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

Pls assist

Thanks Ed G,

You've made my day

Alylia

:

Looks like a syntax problem. Try this:


Purpose_of_Travel: IIf(IsNull(

)," ") & IIf([Code]="0140","Sick
Travel") & IIf([Code]="1452","Conference Travel") &
IIf([Code]="1002","Study Travel")

Ed G





I am working on a query that has a created column based on one column with
codes in a lookup list from a table in the database. This new column named
Purpose_of_Travel should come up with travel purpose based on some codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull([Code])," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study
Travel","
")))

Problem - the null condition is working quite well, but if I select a
particular code from the drop down list or if I enter a wrong code, I am
getting #Error.

Can someone help please?
[/QUOTE][/QUOTE]
[/QUOTE]

 

[Guest]

Duane,

I do not understand how I should go about your suggestion. Can you pls give
me further details on how I can go about it?

What I would like is for the corresponding purpose of travel to come up
against whatever object code i select

Thanks

Why would you want to maintain an expression like this when you could use
JL's solution to maintain a table? I would convert this to a data-driven
solution about the 2nd IIF().

--
Duane Hookom
MS Access MVP
--

Ed G

I have encountered another problem with the syntax below. The code 6145 in
respect of Training Trvl is being highlighted with the error msg "The
expression you entered contains invalid syntax - You may have entered an
operand without an operator "

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

Pls assist

Thanks Ed G,

You've made my day

Alylia

:

Looks like a syntax problem. Try this:


Purpose_of_Travel: IIf(IsNull(

)," ") & IIf([Code]="0140","Sick
Travel") & IIf([Code]="1452","Conference Travel") &
IIf([Code]="1002","Study Travel")

Ed G





I am working on a query that has a created column based on one column
with
codes in a lookup list from a table in the database. This new column
named
Purpose_of_Travel should come up with travel purpose based on some
codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull([Code])," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study
Travel","
")))

Problem - the null condition is working quite well, but if I select a
particular code from the drop down list or if I enter a wrong code, I
am
getting #Error.

Can someone help please?
[/QUOTE][/QUOTE]
[/QUOTE]

 

[Lynn Trapp]

Now the only problem I am encountering now is that with the each purpose
of

travel, I get also "Check Object Code" i.e. the ELSE condition.

As I said in another post (you need to keep your thread together because
it's hard to keep up with what is happening), the "ELSE condition" will fire
EVERY time you have a value because you are concatenating the results of all
the IIF statements. If you insist on using the IIF approach, then use the
syntax I gave you in the other thread.

 

[Duane Hookom]

This wasn't my suggestion. However, you could have a table as suggested by
JL. The code is the primary key in this "lookup" table. You include the
table in your query and join the code fields. This allows you to include the
description as a column in your query.

--
Duane Hookom
MS Access MVP
--

Duane,

I do not understand how I should go about your suggestion. Can you pls
give
me further details on how I can go about it?

What I would like is for the corresponding purpose of travel to come up
against whatever object code i select

Thanks

Why would you want to maintain an expression like this when you could use
JL's solution to maintain a table? I would convert this to a data-driven
solution about the 2nd IIF().

--
Duane Hookom
MS Access MVP
--

Ed G

I have encountered another problem with the syntax below. The code 6145
in
respect of Training Trvl is being highlighted with the error msg "The
expression you entered contains invalid syntax - You may have entered
an
operand without an operator "

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

Pls assist

:

Thanks Ed G,

You've made my day

Alylia

:

Looks like a syntax problem. Try this:


Purpose_of_Travel: IIf(IsNull(

)," ") &
IIf([Code]="0140","Sick
Travel") & IIf([Code]="1452","Conference Travel") &
IIf([Code]="1002","Study Travel")

Ed G





I am working on a query that has a created column based on one
column
with
codes in a lookup list from a table in the database. This new
column
named
Purpose_of_Travel should come up with travel purpose based on some
codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull([Code])," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study
Travel","
")))

Problem - the null condition is working quite well, but if I
select a
particular code from the drop down list or if I enter a wrong
code, I
am
getting #Error.

Can someone help please?
[/QUOTE]
[/QUOTE][/QUOTE]

 

[Guest]

I would like for you to explain more on your suggestion.

I have created a table Travel Purpose with the two fields you suggested -
Object_Code and Description, then link this table to travel details which
also has a field named object_code.

what I actually what is for me to get the description of each travel based
on any selected codes in a query called travel details query. In other words,
I should not be typing the description for each travel, which should come up
with based on the code selected.



al

Hi Alylia,
I would like to suggest a better way to do a lookup. You can create a
lookup table called "Travel_Purpose" or anything you like. The table
definition is as

Table Travel_Purpose:
Code - Text
Description - Text

Then you can put all the code and description in that table.
Table Travel_Purpose:
Code Description
====== =============================
0140 Sick Travel
1452 Conference Travel
1002 Study Travel
.
.
.
etc

Nice thing about to do it this way is that you can add more code if needed
to be.
Then you can join the table based on the "Code" and get your description.

Hope this helps.

I am working on a query that has a created column based on one column with
codes in a lookup list from a table in the database. This new column named
Purpose_of_Travel should come up with travel purpose based on some codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull(

)," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study Travel","
")))

Problem - the null condition is working quite well, but if I select a
particular code from the drop down list or if I enter a wrong code, I am
getting #Error.

Can someone help please?[/QUOTE][/QUOTE]

 

[Guest]

I have tried it and it has solved most of my problem, but since there is the
possibilty that a code can be entered that is invalid, it would come up with
the error "the miscosoft jet engine cannot find a record in the table "Travel
Purpose Table" with key matching field(s) 'Object_Code'"

Can you pls advice on how I can get over this

Al

Hi Alylia,
I would like to suggest a better way to do a lookup. You can create a
lookup table called "Travel_Purpose" or anything you like. The table
definition is as

Table Travel_Purpose:
Code - Text
Description - Text

Then you can put all the code and description in that table.
Table Travel_Purpose:
Code Description
====== =============================
0140 Sick Travel
1452 Conference Travel
1002 Study Travel
.
.
.
etc

Nice thing about to do it this way is that you can add more code if needed
to be.
Then you can join the table based on the "Code" and get your description.

Hope this helps.

I am working on a query that has a created column based on one column with
codes in a lookup list from a table in the database. This new column named
Purpose_of_Travel should come up with travel purpose based on some codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull(

)," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study Travel","
")))

Problem - the null condition is working quite well, but if I select a
particular code from the drop down list or if I enter a wrong code, I am
getting #Error.

Can someone help please?[/QUOTE][/QUOTE]

 

[Duane Hookom]

Don't allow users to enter invalid codes. Use a combo box on a form for
entering the values into the table.

--
Duane Hookom
MS Access MVP

I have tried it and it has solved most of my problem, but since there is the
possibilty that a code can be entered that is invalid, it would come up with
the error "the miscosoft jet engine cannot find a record in the table "Travel
Purpose Table" with key matching field(s) 'Object_Code'"

Can you pls advice on how I can get over this

Al

Hi Alylia,
I would like to suggest a better way to do a lookup. You can create a
lookup table called "Travel_Purpose" or anything you like. The table
definition is as

Table Travel_Purpose:
Code - Text
Description - Text

Then you can put all the code and description in that table.
Table Travel_Purpose:
Code Description
====== =============================
0140 Sick Travel
1452 Conference Travel
1002 Study Travel
.
.
.
etc

Nice thing about to do it this way is that you can add more code if needed
to be.
Then you can join the table based on the "Code" and get your description.

Hope this helps.

I am working on a query that has a created column based on one column with
codes in a lookup list from a table in the database. This new column named
Purpose_of_Travel should come up with travel purpose based on some codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull(

)," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study Travel","
")))

Problem - the null condition is working quite well, but if I select a
particular code from the drop down list or if I enter a wrong code, I am
getting #Error.

Can someone help please?[/QUOTE][/QUOTE][/QUOTE]

 

[Guest]

That is exactly what I have used. But the possiblity of user entering an
invalid code by mistake could crash the program. Is there not a way to get
over this possibilty

Thanks
Al

Don't allow users to enter invalid codes. Use a combo box on a form for
entering the values into the table.

--
Duane Hookom
MS Access MVP

I have tried it and it has solved most of my problem, but since there is the
possibilty that a code can be entered that is invalid, it would come up with
the error "the miscosoft jet engine cannot find a record in the table "Travel
Purpose Table" with key matching field(s) 'Object_Code'"

Can you pls advice on how I can get over this

Al

Hi Alylia,
I would like to suggest a better way to do a lookup. You can create a
lookup table called "Travel_Purpose" or anything you like. The table
definition is as

Table Travel_Purpose:
Code - Text
Description - Text

Then you can put all the code and description in that table.
Table Travel_Purpose:
Code Description
====== =============================
0140 Sick Travel
1452 Conference Travel
1002 Study Travel
.
.
.
etc

Nice thing about to do it this way is that you can add more code if needed
to be.
Then you can join the table based on the "Code" and get your description.

Hope this helps.

:

I am working on a query that has a created column based on one column with
codes in a lookup list from a table in the database. This new column named
Purpose_of_Travel should come up with travel purpose based on some codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull(

)," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study Travel","
")))

Problem - the null condition is working quite well, but if I select a
particular code from the drop down list or if I enter a wrong code, I am
getting #Error.

Can someone help please?[/QUOTE][/QUOTE]
[/QUOTE]

 

[Guest]

The same is also true for null values

Thx

Don't allow users to enter invalid codes. Use a combo box on a form for
entering the values into the table.

--
Duane Hookom
MS Access MVP

I have tried it and it has solved most of my problem, but since there is the
possibilty that a code can be entered that is invalid, it would come up with
the error "the miscosoft jet engine cannot find a record in the table "Travel
Purpose Table" with key matching field(s) 'Object_Code'"

Can you pls advice on how I can get over this

Al

Hi Alylia,
I would like to suggest a better way to do a lookup. You can create a
lookup table called "Travel_Purpose" or anything you like. The table
definition is as

Table Travel_Purpose:
Code - Text
Description - Text

Then you can put all the code and description in that table.
Table Travel_Purpose:
Code Description
====== =============================
0140 Sick Travel
1452 Conference Travel
1002 Study Travel
.
.
.
etc

Nice thing about to do it this way is that you can add more code if needed
to be.
Then you can join the table based on the "Code" and get your description.

Hope this helps.

:

I am working on a query that has a created column based on one column with
codes in a lookup list from a table in the database. This new column named
Purpose_of_Travel should come up with travel purpose based on some codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull(

)," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study Travel","
")))

Problem - the null condition is working quite well, but if I select a
particular code from the drop down list or if I enter a wrong code, I am
getting #Error.

Can someone help please?[/QUOTE][/QUOTE]
[/QUOTE]

 

[Duane Hookom]

The system should not "crash". Use a join to the lookup table that shows all
the records from your main table. Where you would display the description,
use:
TravelPurpose:Nz([Travel Purpose],"N/A")

--
Duane Hookom
MS Access MVP
--

That is exactly what I have used. But the possiblity of user entering an
invalid code by mistake could crash the program. Is there not a way to get
over this possibilty

Thanks
Al

Don't allow users to enter invalid codes. Use a combo box on a form for
entering the values into the table.

--
Duane Hookom
MS Access MVP

I have tried it and it has solved most of my problem, but since there
is the
possibilty that a code can be entered that is invalid, it would come up with
the error "the miscosoft jet engine cannot find a record in the table "Travel
Purpose Table" with key matching field(s) 'Object_Code'"

Can you pls advice on how I can get over this

Al
:

Hi Alylia,
I would like to suggest a better way to do a lookup. You can create
a
lookup table called "Travel_Purpose" or anything you like. The table
definition is as

Table Travel_Purpose:
Code - Text
Description - Text

Then you can put all the code and description in that table.
Table Travel_Purpose:
Code Description
====== =============================
0140 Sick Travel
1452 Conference Travel
1002 Study Travel
.
.
.
etc

Nice thing about to do it this way is that you can add more code if needed
to be.
Then you can join the table based on the "Code" and get your description.

Hope this helps.

:

I am working on a query that has a created column based on one
column with
codes in a lookup list from a table in the database. This new
column named
Purpose_of_Travel should come up with travel purpose based on some codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull(

)," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study Travel","
")))

Problem - the null condition is working quite well, but if I select
a
particular code from the drop down list or if I enter a wrong code,
I am
getting #Error.

Can someone help please?[/QUOTE]
[/QUOTE][/QUOTE]

 

[Guest]

Duane,

I think the lookup table would be the solution to my problem, but as I
mentioned earlier, I would the program to adequately handle any possible
error resulting from invalid code.

What I have are three tables
- staff table, travel details table and travel purpose table

travel details table has a number of fields include object_code which is the
primary key of travel purpose table (the other being travel_purpose).

I also have a travel details query which has all of my fields from the
travel details table plus travel purpose from the travel purpose table.

if I enter an object_code in the query the travel purpose comes up
automatically against the code. I have created a form which is linked to this
query for data input. So far I have been somewhat successful, but I need to
ensure that whenever an invalid code is entered it can be handled without the
msg "the miscosoft jet engine cannot find a record in the table "Travel
Purpose Table" with key matching field(s) 'Object_Code'" not coming up.

Please assist

Alylia

The system should not "crash". Use a join to the lookup table that shows all
the records from your main table. Where you would display the description,
use:
TravelPurpose:Nz([Travel Purpose],"N/A")

--
Duane Hookom
MS Access MVP
--

That is exactly what I have used. But the possiblity of user entering an
invalid code by mistake could crash the program. Is there not a way to get
over this possibilty

Thanks
Al

Don't allow users to enter invalid codes. Use a combo box on a form for
entering the values into the table.

--
Duane Hookom
MS Access MVP


I have tried it and it has solved most of my problem, but since there
is
the
possibilty that a code can be entered that is invalid, it would come up
with
the error "the miscosoft jet engine cannot find a record in the table
"Travel
Purpose Table" with key matching field(s) 'Object_Code'"

Can you pls advice on how I can get over this

Al
:

Hi Alylia,
I would like to suggest a better way to do a lookup. You can create
a
lookup table called "Travel_Purpose" or anything you like. The table
definition is as

Table Travel_Purpose:
Code - Text
Description - Text

Then you can put all the code and description in that table.
Table Travel_Purpose:
Code Description
====== =============================
0140 Sick Travel
1452 Conference Travel
1002 Study Travel
.
.
.
etc

Nice thing about to do it this way is that you can add more code if
needed
to be.
Then you can join the table based on the "Code" and get your
description.

Hope this helps.

:

I am working on a query that has a created column based on one
column
with
codes in a lookup list from a table in the database. This new
column
named
Purpose_of_Travel should come up with travel purpose based on some
codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull(

)," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference Travel",IIf([Code]=1002,"Study
Travel","
")))

Problem - the null condition is working quite well, but if I select
a
particular code from the drop down list or if I enter a wrong code,
I
am
getting #Error.

Can someone help please?
[/QUOTE][/QUOTE]
[/QUOTE]

 

[Duane Hookom]

Don't include the TravelPurpose table in your form's record source. Just use
a combo box that has a row source of the TravelPurpose table and set the
properties of the combo box to limit to list.

--
Duane Hookom
MS Access MVP
--

Duane,

I think the lookup table would be the solution to my problem, but as I
mentioned earlier, I would the program to adequately handle any possible
error resulting from invalid code.

What I have are three tables
- staff table, travel details table and travel purpose table

travel details table has a number of fields include object_code which is
the
primary key of travel purpose table (the other being travel_purpose).

I also have a travel details query which has all of my fields from the
travel details table plus travel purpose from the travel purpose table.

if I enter an object_code in the query the travel purpose comes up
automatically against the code. I have created a form which is linked to
this
query for data input. So far I have been somewhat successful, but I need
to
ensure that whenever an invalid code is entered it can be handled without
the
msg "the miscosoft jet engine cannot find a record in the table "Travel
Purpose Table" with key matching field(s) 'Object_Code'" not coming up.

Please assist

Alylia

The system should not "crash". Use a join to the lookup table that shows
all
the records from your main table. Where you would display the
description,
use:
TravelPurpose:Nz([Travel Purpose],"N/A")

--
Duane Hookom
MS Access MVP
--

That is exactly what I have used. But the possiblity of user entering
an
invalid code by mistake could crash the program. Is there not a way to
get
over this possibilty

Thanks
Al

:

Don't allow users to enter invalid codes. Use a combo box on a form
for
entering the values into the table.

--
Duane Hookom
MS Access MVP


I have tried it and it has solved most of my problem, but since
there
is
the
possibilty that a code can be entered that is invalid, it would come
up
with
the error "the miscosoft jet engine cannot find a record in the
table
"Travel
Purpose Table" with key matching field(s) 'Object_Code'"

Can you pls advice on how I can get over this

Al
:

Hi Alylia,
I would like to suggest a better way to do a lookup. You can
create
a
lookup table called "Travel_Purpose" or anything you like. The
table
definition is as

Table Travel_Purpose:
Code - Text
Description - Text

Then you can put all the code and description in that table.
Table Travel_Purpose:
Code Description
====== =============================
0140 Sick Travel
1452 Conference Travel
1002 Study Travel
.
.
.
etc

Nice thing about to do it this way is that you can add more code
if
needed
to be.
Then you can join the table based on the "Code" and get your
description.

Hope this helps.

:

I am working on a query that has a created column based on one
column
with
codes in a lookup list from a table in the database. This new
column
named
Purpose_of_Travel should come up with travel purpose based on
some
codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull(

)," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference
Travel",IIf([Code]=1002,"Study
Travel","
")))

Problem - the null condition is working quite well, but if I
select
a
particular code from the drop down list or if I enter a wrong
code,
I
am
getting #Error.

Can someone help please?
[/QUOTE]
[/QUOTE][/QUOTE]

 

[Guest]

Thanks Duane.

That was all I need to do the final trick.

I am profoundly grateful for your support

AL

Don't include the TravelPurpose table in your form's record source. Just use
a combo box that has a row source of the TravelPurpose table and set the
properties of the combo box to limit to list.

--
Duane Hookom
MS Access MVP
--

Duane,

I think the lookup table would be the solution to my problem, but as I
mentioned earlier, I would the program to adequately handle any possible
error resulting from invalid code.

What I have are three tables
- staff table, travel details table and travel purpose table

travel details table has a number of fields include object_code which is
the
primary key of travel purpose table (the other being travel_purpose).

I also have a travel details query which has all of my fields from the
travel details table plus travel purpose from the travel purpose table.

if I enter an object_code in the query the travel purpose comes up
automatically against the code. I have created a form which is linked to
this
query for data input. So far I have been somewhat successful, but I need
to
ensure that whenever an invalid code is entered it can be handled without
the
msg "the miscosoft jet engine cannot find a record in the table "Travel
Purpose Table" with key matching field(s) 'Object_Code'" not coming up.

Please assist

Alylia

The system should not "crash". Use a join to the lookup table that shows
all
the records from your main table. Where you would display the
description,
use:
TravelPurpose:Nz([Travel Purpose],"N/A")

--
Duane Hookom
MS Access MVP
--

That is exactly what I have used. But the possiblity of user entering
an
invalid code by mistake could crash the program. Is there not a way to
get
over this possibilty

Thanks
Al

:

Don't allow users to enter invalid codes. Use a combo box on a form
for
entering the values into the table.

--
Duane Hookom
MS Access MVP


I have tried it and it has solved most of my problem, but since
there
is
the
possibilty that a code can be entered that is invalid, it would come
up
with
the error "the miscosoft jet engine cannot find a record in the
table
"Travel
Purpose Table" with key matching field(s) 'Object_Code'"

Can you pls advice on how I can get over this

Al
:

Hi Alylia,
I would like to suggest a better way to do a lookup. You can
create
a
lookup table called "Travel_Purpose" or anything you like. The
table
definition is as

Table Travel_Purpose:
Code - Text
Description - Text

Then you can put all the code and description in that table.
Table Travel_Purpose:
Code Description
====== =============================
0140 Sick Travel
1452 Conference Travel
1002 Study Travel
.
.
.
etc

Nice thing about to do it this way is that you can add more code
if
needed
to be.
Then you can join the table based on the "Code" and get your
description.

Hope this helps.

:

I am working on a query that has a created column based on one
column
with
codes in a lookup list from a table in the database. This new
column
named
Purpose_of_Travel should come up with travel purpose based on
some
codes .

Below is an example of what I have been working with:

Purpose_of_Travel: IIf(IsNull(

)," ",IIf([Code]=0140,"Sick
Travel",IIf([Code]=1452,"Conference
Travel",IIf([Code]=1002,"Study
Travel","
")))

Problem - the null condition is working quite well, but if I
select
a
particular code from the drop down list or if I enter a wrong
code,
I
am
getting #Error.

Can someone help please?
[/QUOTE][/QUOTE]
[/QUOTE]