Using IIF in an append query, invalid syntax

[securitywyrm]

I'm trying to use an append query that will help 'clean up' some inconsistent data I get on a gender flag. I get M, Male, F, Fem, Female, etc. So I'd like the append query when moving from the 'bad' data to my 'active' data to use an IIF command.


The "bad" data field is called "Gender (M/F)", I can't change this.

I tried this
[EXPR3]=IIf([Gender (M/F)]=Like * & "F" & *,"Female","Male")
But get an "invalid syntax" error. Any help on what I'm doing wrong would be helpful. The idea is that if the letter F is in the field, put female, otherwise put male.

[June7]

Put the wildcards within the quotes, no equal sign with LIKE.

IIf([Gender (M/F)] Like "*F*","Female","Male")

Advise not to use spaces nor punctuation/special characters in naming convention.

[securitywyrm]

Put the wildcards within the quotes, no equal sign with LIKE.

IIf([Gender (M/F)] Like "*F*","Female","Male")

Advise not to use spaces nor punctuation/special characters in naming convention.

Thank you!

And indeed, I don't use spacing and punctuation in my naming conventions. That's why I'm importing the data from 'their' data tables to MY data tables.

[securitywyrm]

I also tried
EXPR3: IIf([MasterRollupFile].[Gender (M/F)] Like "*F*","Female","Male")
But it still prompts me for parameter value.

[JoeM]

I also tried
EXPR3: IIf([MasterRollupFile].[Gender (M/F)] Like "*F*","Female","Male")
But it still prompts me for parameter value.

I set up these EXACT same structure, and it worked for without issue.

If you are getting prompted, it means that it does NOT recognize your field name. Something is slightly different (it could be something as simple as an extra space).

Please do the following.
Create a real simple query, where you simply select (be sure to select it, not type it) this Gender field to display, and confirm that works.
If it does (which it should), change the query to SQL View and copy and paste the code for that query here so we can see it.

[securitywyrm]

I set up these EXACT same structure, and it worked for without issue.

If you are getting prompted, it means that it does NOT recognize your field name. Something is slightly different (it could be something as simple as an extra space).

Please do the following.
Create a real simple query, where you simply select (be sure to select it, not type it) this Gender field to display, and confirm that works.
If it does (which it should), change the query to SQL View and copy and paste the code for that query here so we can see it.

Thank you, it was indeed a missing space.