Change return value to text

[SteveV]

I have a query that is returning a numeric value for a field. I would like to convert that number to a specific text string. For example, if the query returns a 1 then I want the query to display the word Pass, if it returns a 2 then display Fail etc... I was tring to use a Case select statement for this but I could not get the syntax correct. Has anyone done something like this in one of their queries?

 

[jwindon]

Where are the results "Pass" & "Fail" to be displayed?

Are only values of 1 and 2 returned? Would a yes/no data type serve better in this case?

 

[Pat Hartman]

You can't use a Select Case statement in a query. Select Case is a VBA language construct. However, you can use functions in queries. The IIf() would do what you need but a simpler function and the one I would choose is the Choose() function. It works well with a small list of numeric values. Look up the syntax in help.

 

[SteveV]

Pat,

Thats a slick fix, it works great! In case anyone else needs to do this here is the actual syntax you need to use for your SQL statement:

SELECT Table1.firstfield,=Choose([numberfield],"One","Two","Three","Four") AS Whatever,
FROM Table1;

What this does is convert an integer number in the numberfield field to a string. So for example if the field contains a 1 then One will be displayed in the query return. If the field contains 2 then Two will display and so on.

Thanks again to Pat!!