IIF syntax error

[RexesOperator]

I am getting a syntax error using this:

=IIf([COUNTRY] <>"CANADA" Or "USA", [TELEPHONENOTUSCAN], Replace(Format([TELEPHONE] "(###)-###-####")))

I am trying to use the same text box for North American conventions, and everyone else. The text box is not formatted, but I want North American phone numbers to look formatted.

 

[Mike375]

I think IIf([COUNTRY] <>"CANADA" Or "USA", should be

IIf([COUNTRY] <>"CANADA" Or [COUNTRY] Like "USA",

Not sure about the last part Fromat etc

 

[speakers_86]

The problem is with the "or USA" part at the beginning. Dont know the correct answer of the top of my head, but it should be something like

not canada and not usa

I always have to play to get the operators to work.

 

[speakers_86]

Actually, maybe its

not canada or not usa

Like I said, I need to play with it to see what works. Im sure you can get it now!

 

[RexesOperator]

That didn't work either. I also tried a simpler form:

=IIf([COUNTRY] <>"CANADA", [TELEPHONENOTUSCAN], Replace(Format([TELEPHONE] "###-###-####")))

This doesn't give the USA phone numbers, but it will others:

=IIf([COUNTRY] <>"CANADA" or "USA", [TELEPHONENOTUSCAN], [TELEPHONE])

And I tried deleting the text box and creating a new one.

 

[RexesOperator]

Actually, maybe its

not canada or not usa

Like I said, I need to play with it to see what works. Im sure you can get it now!

That did it. At least now I don't feel so foolish.

 

[speakers_86]

Dont worry! Just remember, if you have "not" with 2 criteria, then the not does not carry over to the second criteria. (????)

not canada or usa

This says "Results should NOT be Canada or they Should be USA"
Notice how the not only applies to the first criteria.

not canada or not usa

"Results should NOT be Canada or they should NOT be USA"
Basically, filter out Canada, then filter out USA
This leaves the rest of the world!

 

[WayneRyan]

RO,

=IIf([COUNTRY] <> "CANADA" And [COUNTRY] <> "USA", [TELEPHONENOTUSCAN], Format([TELEPHONE] "(000)-000-0000"))

Wayne

 

[RexesOperator]

I wonder if <> ("CANADA" AND "USA")? I will try that one out. I did get it working using your method.

 

[speakers_86]

Rex-I dont think that will work. Access will prob interpret that as one criteria.

It looks like Waynes should work too.

 

[RexesOperator]

It did. But I am happy with what I have.