3 IIF conditions with OR 2

[leicklda]

I am trying to analyze ZIP codes. I need to set the following criteria:

If the first # =
1
or
4
or
Null

Then set the value to the first three numbers of the ZIP code.

If the first number is any other value than these 3 listed, then set the value to 999.

Here is what I'm trying and it is not working - it is setting everything to 999

ZIP3: IIf((Left([zip],1)<>4) Or (Left([zip],1)<>1) Or (IsNull([zip])),999,Left([zip],3))

Thanks for any help!!

 

[wvmbark]

You might try

ZIP3: IIf((Left([zip],1)In("4","1","") ,999,Left([zip],3))

Mike

 

[leicklda]

Thanks Mike

I tried it and I keep getting a syntax error. I've tried rearranging parentheses, quotes, etc... to no avail.

Any other ideas?
Thanks

 

[CosmoKramer]

You might want to reexamine your logic and test for equal to and set your value instead of looking for not equal to. Something like this:

ZIP3: IIf((Left([zip],1)=4) Or (Left([zip],1)=1) Or (IsNull([zip])),Left([zip],3),999)

Hoc nomen meum verum non est.

 

[BNPMike]

Cosmo is right. It's no use saying "If it is not 1 or not 4 or not null" (which you've got wrong anyway). You need to say "If it is not 1 AND not 4 AND not null"

 

[leicklda]

Works like a dream... thanks!

 

[BNPMike]

leicklda
You're very generous with your stars. Anytime you've got a problem, let us know.