help needed using the switch function in a query

[martinr]

i am getting a syntax error when trying to run an access select query - the query works fine until i include this Switch statement;

genderStatus: Switch([gender]="M","Male",[gender]="F","Female",[gender]="X","Cross gender")

should this sql statement work?

 

[MajP]

lot easier to make a simple table
TblGender
genderID
genderDescription

Then link to that. Example

M Male
F Female
X Cross Gender

 

[arnelgp]

you can use a table as the other contributor suggested, and use DLookup() function in your query.
Or you may expand the Switch for "unspecified" gender.

genderStatus: Switch([gender]="M","Male",[gender]="F","Female",[gender]="X","Cross gender", True, "Unspecified")

 

[The_Doc_Man]

I'll toss in a motivator. Make it a table like MajP suggested. If you are using a one-character code, even a short table to act as translator via a JOIN of some flavor makes sense if you ever decide you wanted to have this as an option in a combo box. Because if you go that route, the translator table could drive that control trivially as its RowSource.

 

[deletedT]

I should add that I have an entire sub-system that manages this type of lookup. I add it to every new application I build. I'm sure I've posted it here a dozen times.

How can I search for it?
Do you have a link to one of those posts?

thank you.

 

[deletedT]

Million thanks. I'll take a look.