if.. then.. else.. in a query

K

Kim K

I'm not even sure it's possible to calculate the value of a field based on
other criteria in a table but this is basically what I'm trying to do:

If ND_IND = 1 and AND_IND = 0 then
Derived GP = 2
Else if ND_IND = 0 and AND_IND = 1 then
Derived GP = 1
Else if ND_IND = 0 and AND_IND = 0 then
Derived GP = blank

How exactly do I do this (I'm sure my syntax is all wrong) and where do I
put it?
 
D

Dale Fye

Kim,

To do this in a query, I prefer to use the Switch function, which evaluates
a series of expressions and returns the appropriate value:

1. Create a new query and add the table and any of the other fields you
want from it to the query.

2. In a new column, type:

Derived_GP: Switch([ND_IND]=1 AND [AND_IND] = 0, 2,
[ND_IND]=0 AND [AND_IND] = 1, 1,
[ND_IND]=0 AND [AND_IND] = 0, NULL,
True, -1)

Note: I've wrapped the text, but it should all go on one line.
Additionally, I added the final value test to alert you to those records
where none of these conditions is met. The Switch( ) function will return a
NULL if none of the conditions are met, and since you are returning a Null
for your final condition, I thought it important that you be alerted if none
of the conditions is met.

HTH
Dale
 
K

KARL DEWEY

Use nested IIF statement.
Derived GP: IIF([ND_IND] = 1 AND [AND_IND] = 0, 2, IIF([ND_IND] = 0 AND
[AND_IND] = 1, 1, IIF([ND_IND] = 0 AND [AND_IND] = 0, "", "Error")))
 
K

Kim K

Thank you so very much! That worked perfectly! :)

Dale Fye said:
Kim,

To do this in a query, I prefer to use the Switch function, which evaluates
a series of expressions and returns the appropriate value:

1. Create a new query and add the table and any of the other fields you
want from it to the query.

2. In a new column, type:

Derived_GP: Switch([ND_IND]=1 AND [AND_IND] = 0, 2,
[ND_IND]=0 AND [AND_IND] = 1, 1,
[ND_IND]=0 AND [AND_IND] = 0, NULL,
True, -1)

Note: I've wrapped the text, but it should all go on one line.
Additionally, I added the final value test to alert you to those records
where none of these conditions is met. The Switch( ) function will return a
NULL if none of the conditions are met, and since you are returning a Null
for your final condition, I thought it important that you be alerted if none
of the conditions is met.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Kim K said:
I'm not even sure it's possible to calculate the value of a field based on
other criteria in a table but this is basically what I'm trying to do:

If ND_IND = 1 and AND_IND = 0 then
Derived GP = 2
Else if ND_IND = 0 and AND_IND = 1 then
Derived GP = 1
Else if ND_IND = 0 and AND_IND = 0 then
Derived GP = blank

How exactly do I do this (I'm sure my syntax is all wrong) and where do I
put it?
Click to expand...
Click to expand...
 
K

Kim K

That worked as well! Thank you!

KARL DEWEY said:
Use nested IIF statement.
Derived GP: IIF([ND_IND] = 1 AND [AND_IND] = 0, 2, IIF([ND_IND] = 0 AND
[AND_IND] = 1, 1, IIF([ND_IND] = 0 AND [AND_IND] = 0, "", "Error")))

--
KARL DEWEY
Build a little - Test a little


Kim K said:
I'm not even sure it's possible to calculate the value of a field based on
other criteria in a table but this is basically what I'm trying to do:

If ND_IND = 1 and AND_IND = 0 then
Derived GP = 2
Else if ND_IND = 0 and AND_IND = 1 then
Derived GP = 1
Else if ND_IND = 0 and AND_IND = 0 then
Derived GP = blank

How exactly do I do this (I'm sure my syntax is all wrong) and where do I
put it?
Click to expand...
Click to expand...
 
K

Kim K

I ran a Make Table query using this derived field. Now, I need to join the
table to another table using Derived GP field that has the description for
each value:

Number Description
1 General
2 Specific
3 No Match

However, I can't get it to return any results. Both the Derived GP field
and Number are text type with a length of 1. I can't figure out what I'm
doing wrong.

Dale Fye said:
Kim,

To do this in a query, I prefer to use the Switch function, which evaluates
a series of expressions and returns the appropriate value:

1. Create a new query and add the table and any of the other fields you
want from it to the query.

2. In a new column, type:

Derived_GP: Switch([ND_IND]=1 AND [AND_IND] = 0, 2,
[ND_IND]=0 AND [AND_IND] = 1, 1,
[ND_IND]=0 AND [AND_IND] = 0, NULL,
True, -1)

Note: I've wrapped the text, but it should all go on one line.
Additionally, I added the final value test to alert you to those records
where none of these conditions is met. The Switch( ) function will return a
NULL if none of the conditions are met, and since you are returning a Null
for your final condition, I thought it important that you be alerted if none
of the conditions is met.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Kim K said:
I'm not even sure it's possible to calculate the value of a field based on
other criteria in a table but this is basically what I'm trying to do:

If ND_IND = 1 and AND_IND = 0 then
Derived GP = 2
Else if ND_IND = 0 and AND_IND = 1 then
Derived GP = 1
Else if ND_IND = 0 and AND_IND = 0 then
Derived GP = blank

How exactly do I do this (I'm sure my syntax is all wrong) and where do I
put it?
Click to expand...
Click to expand...
 
K

Kim K

Never mind -- I figured it out!

Kim K said:
I ran a Make Table query using this derived field. Now, I need to join the
table to another table using Derived GP field that has the description for
each value:

Number Description
1 General
2 Specific
3 No Match

However, I can't get it to return any results. Both the Derived GP field
and Number are text type with a length of 1. I can't figure out what I'm
doing wrong.

Dale Fye said:
Kim,

To do this in a query, I prefer to use the Switch function, which evaluates
a series of expressions and returns the appropriate value:

1. Create a new query and add the table and any of the other fields you
want from it to the query.

2. In a new column, type:

Derived_GP: Switch([ND_IND]=1 AND [AND_IND] = 0, 2,
[ND_IND]=0 AND [AND_IND] = 1, 1,
[ND_IND]=0 AND [AND_IND] = 0, NULL,
True, -1)

Note: I've wrapped the text, but it should all go on one line.
Additionally, I added the final value test to alert you to those records
where none of these conditions is met. The Switch( ) function will return a
NULL if none of the conditions are met, and since you are returning a Null
for your final condition, I thought it important that you be alerted if none
of the conditions is met.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Kim K said:
I'm not even sure it's possible to calculate the value of a field based on
other criteria in a table but this is basically what I'm trying to do:

If ND_IND = 1 and AND_IND = 0 then
Derived GP = 2
Else if ND_IND = 0 and AND_IND = 1 then
Derived GP = 1
Else if ND_IND = 0 and AND_IND = 0 then
Derived GP = blank

How exactly do I do this (I'm sure my syntax is all wrong) and where do I
put it?
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

No need for make table just plug the text in the nested IIF statement.
Derived GP: IIF([ND_IND] = 1 AND [AND_IND] = 0, "Specific", IIF([ND_IND] =
0 AND [AND_IND] = 1, "General", IIF([ND_IND] = 0 AND [AND_IND] = 0, "No
Match", "Error")))

--
KARL DEWEY
Build a little - Test a little


Kim K said:
I ran a Make Table query using this derived field. Now, I need to join the
table to another table using Derived GP field that has the description for
each value:

Number Description
1 General
2 Specific
3 No Match

However, I can't get it to return any results. Both the Derived GP field
and Number are text type with a length of 1. I can't figure out what I'm
doing wrong.

Dale Fye said:
Kim,

To do this in a query, I prefer to use the Switch function, which evaluates
a series of expressions and returns the appropriate value:

1. Create a new query and add the table and any of the other fields you
want from it to the query.

2. In a new column, type:

Derived_GP: Switch([ND_IND]=1 AND [AND_IND] = 0, 2,
[ND_IND]=0 AND [AND_IND] = 1, 1,
[ND_IND]=0 AND [AND_IND] = 0, NULL,
True, -1)

Note: I've wrapped the text, but it should all go on one line.
Additionally, I added the final value test to alert you to those records
where none of these conditions is met. The Switch( ) function will return a
NULL if none of the conditions are met, and since you are returning a Null
for your final condition, I thought it important that you be alerted if none
of the conditions is met.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Kim K said:
I'm not even sure it's possible to calculate the value of a field based on
other criteria in a table but this is basically what I'm trying to do:

If ND_IND = 1 and AND_IND = 0 then
Derived GP = 2
Else if ND_IND = 0 and AND_IND = 1 then
Derived GP = 1
Else if ND_IND = 0 and AND_IND = 0 then
Derived GP = blank

How exactly do I do this (I'm sure my syntax is all wrong) and where do I
put it?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with ranking in a query 2
IIF Error: invalid syntax, may have entered a comma w/o preceding 1
rank in select query not right 1
INSERT ... WHERE NOT EXISTS ... 3
Doesn't recognize Crosstab gen Fields 5
If/Then/Else Query 6
IIF Function & Calculation 2
query update 3

Top