How to write IIF statement

[mattc66 via AccessMonster.com]

I am doing an update query. If the Buyer is not a #6 or #7 I want it be #2.

My Field is: BUYER: IIf([BUYER]<>6 and <>7,2)

it doesn't like the And or Or.

 

[vanderghast]

Under the field Buyer, the criteria is:

<> 6 AND <> 7

and the Update to line is:

2


You can change the criteria to:

NOT IN(6, 7)


if you prefer.


It is the query as a whole which should to the update, NOT an iif lost in
the criteria.



Vanderghast, Access MVP

 

[Douglas J. Steele]

My Field is: BUYER: IIf([NameOfTable].[BUYER]<>6 and
[NameOfTable].[BUYER]<>7,2, [NameOfTable].[BUYER])

You need to repeat the field name each time (or else use NOT IN)

My Field is: BUYER: IIf([NameOfTable].[BUYER] NOT IN (6, 7),2
,[NameOfTable].[BUYER])

You need to include [NameOfTable]. so that it's not a circular reference to
Access.

You need to include [NameOfTable].[Buyer] for the False condition, or you
won't get a value if it isn't 6 or 7.

 

[Douglas J. Steele]

Use Vanderghast's solution. I'd missed the fact you were trying to do this
in an Update query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

My Field is: BUYER: IIf([NameOfTable].[BUYER]<>6 and
[NameOfTable].[BUYER]<>7,2, [NameOfTable].[BUYER])

You need to repeat the field name each time (or else use NOT IN)

My Field is: BUYER: IIf([NameOfTable].[BUYER] NOT IN (6, 7),2
,[NameOfTable].[BUYER])

You need to include [NameOfTable]. so that it's not a circular reference
to Access.

You need to include [NameOfTable].[Buyer] for the False condition, or you
won't get a value if it isn't 6 or 7.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am doing an update query. If the Buyer is not a #6 or #7 I want it be
#2.

My Field is: BUYER: IIf([BUYER]<>6 and <>7,2)

it doesn't like the And or Or.