IIF Statement 2

[Skins64]

Help Please....probably is simple for most of you.

I have a field called Public...if the company is Public the field returns a P if it is Private then it returns an N and if it is blank then of course it is blank.

I need to write a IIf statement the replaces fields that contain the letter P with the word Public, the letter N with Private and the fields that are blank need to remain blank.

How would I write this IIf statement?

Thanks

 

[BSman]

IIF("[Public] = 'P'","Public","Private")

This assumes that you are using this in a query on your table. If you are writing it in VBA code, then you need to have a text box on your form/report that has the field called Public as it's data source. The IIF statement would then read:

IIF("me.txtPublic = 'P'","Public","Private")

Bob S.

 

[PHV]

Expr1: IIf([Public]='P', 'Public', IIf([Public]='N', 'Private', ''))

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

 

[Skins64]

That worked!....thanks guys....

how can I add to that expression without getting an error message telling me my string is invalid can only be 2048 characters long" ???

For instance, if a field has three options U for Union, N for NonUnion or D for Double Breasted?

 

[BSman]

IIF("[Union] = 'U'","Union",IIF("[Union] = 'N'","NonUnion","Double Breasted"))

You can nest the statements like this.

Double Breasted???????

Bob S.

 

[Skins64]

This did not work it updated every field to Union.

However, the way you have set this up....

Wouldn't this cause fields that are blank to be updated to Double Breasted? (D=doublebreasted)

Double Breasted, btw, means a contractor is Union but operates a 2nd Non-Union company for financial purposes.

 

[PHV]

The correct syntax:
IIf([Union]='U','Union',IIf([Union]='N','NonUnion',IIf([Union]='D','Double Breasted','')))

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

 

[Skins64]

The correct syntax:
IIf([Union]='U','Union',IIf([Union]='N','NonUnion',IIf([Union]='D','Double Breasted','')))

Thanks, works great!