changing field value if it has a certain value 1

[Thingol]

Hi there,

I want to create a query in which the field [gender] is evaluated (it can have three values: "Male", "Female" or "Male/Female"). I want the query to show "Sir" if the value is "Male", "Madam" if the value is "Female" and "Sir/Madam" if the value is "Male/Female".

I have been trying all sorts of things with IIF statements, but I keep getting syntax errors, is there any easier way I can do this?

Thanks a lot.

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[mordja]

Thingol,

Something like

IIf([Gender]="Male","Sir",IIf([Gender]="Female","Madam","Sir/Madam"))

Assumes only three values, anything not "Male" or "Female" will be "Sir/Madam" including Null values.

MOrdja

 

[Thingol]

Hi Mordja,

Thanks for your reply. You state the exact same line, I used myself! (Great to see I'm not gone insane yet.) Possibly I am using it in the wrong place. Where should I put this line? I have tried using it in the criteria for the field "Gender"

Best regards,
Martijn.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[mordja]

No it goes in the field. You might want to give it a name. ie

Title: IIf([Gender]="Male","Sir",IIf([Gender]="Female","Madam","Sir/Madam"))

Put that in the field of a query.

Mordja

 

[Thingol]

Hi Mordja,

Thanks again. It still states that the syntax is wrong. I think it has something to do with the nesting of the iif statements, because it focuses on one of the commas after clicking OK on the error messagebox.

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[PHV]

Try to replace the commas (,) by semicolons (

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244