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

 

[Pat Hartman]

If you use the table suggested by MajP, you would never use DLookup() to get the value, you would use a left join which is much more efficient. In the past, I wouldn't have had a problem with using Switch for something like this where the possible value set was small and unchanging but in the world today, it is popular to think of gender as fluid and having many more options than what you are using. If you are working in an environment like that, go with the table so you can let the users add genders at will rather than requiring you to change queries, code, forms, etc.

 

[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.

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

I included a PowerPoint that explains a little of the functionality

 

[deletedT]

Million thanks. I'll take a look.

 

[Pat Hartman]

You're welcome.