Creating New Field and Conditionally Setting it's Value

[Guest]

I have a report that is based on a query.

I have a "Candidate Status" field that has one of five different values
"value 1", "value 2", "value 3", "value 4", "value 5"

In this report I want to display Value 1, or Value 2 or Value 3.
If the Candidate Status field equals value 4 or value 5, I want to display
value 3 instead for those record.

What's the best way to do this?? Creating a New Field in the query?

Thanks - Kathleen

 

[Brendan Reynolds]

SELECT IIf([CandidateStatus] In ("value 1","value 2","value
3"),[CandidateStatus],"value 3") AS Expr1
FROM Table1;

 

[Guest]

Ok - I left out one piece of info that I think will affect your solution.
There are more values to the Candidate Status field than just Value 1-5 -
there are values 6, 7, 8. 9 and it could grow in the future.....

Kathleen

SELECT IIf([CandidateStatus] In ("value 1","value 2","value
3"),[CandidateStatus],"value 3") AS Expr1
FROM Table1;

--
Brendan Reynolds

I have a report that is based on a query.

I have a "Candidate Status" field that has one of five different values
"value 1", "value 2", "value 3", "value 4", "value 5"

In this report I want to display Value 1, or Value 2 or Value 3.
If the Candidate Status field equals value 4 or value 5, I want to display
value 3 instead for those record.

What's the best way to do this?? Creating a New Field in the query?

Thanks - Kathleen

 

[Brendan Reynolds]

Well, you've told us what you want to happen if the value is "Value 1",
"Value 2", or "Value 3" (display the value) and you've told us what you want
to happen if the value is "Value 4" or "Value 5" (display "Value 3") but you
have not said what you want to happen if the value is none of the above. As
I don't know what you want to happen if the value is "Value 6" or above, I
can't say whether that affects the solution or not, or if so, how.

You could reverse the logic if that helps ...

IIf([CandidateStatus] In ("Value 4", "Value 5"), "Value 3",
[CandidateStatus])

--
Brendan Reynolds

Ok - I left out one piece of info that I think will affect your solution.
There are more values to the Candidate Status field than just Value 1-5 -
there are values 6, 7, 8. 9 and it could grow in the future.....

Kathleen

SELECT IIf([CandidateStatus] In ("value 1","value 2","value
3"),[CandidateStatus],"value 3") AS Expr1
FROM Table1;

--
Brendan Reynolds

I have a report that is based on a query.

I have a "Candidate Status" field that has one of five different values
"value 1", "value 2", "value 3", "value 4", "value 5"

In this report I want to display Value 1, or Value 2 or Value 3.
If the Candidate Status field equals value 4 or value 5, I want to
display
value 3 instead for those record.

What's the best way to do this?? Creating a New Field in the query?

Thanks - Kathleen