IIF statement in Access 2000

[nospace52]

I'm putting together a database which has employee's Name and also a field called remarks.
In the Remarks field could be APPT/AM and I'm trying to use
the IIF statement to set the remarks to OUT if it matches.

This is the sql but when I run it nothing appears in the REMARKS field.
SELECT TestIf.Remarks
FROM TestIf
WHERE ((([TESTIF].[REMARKS])=IIf([TESTIF].[REMARKS]="APPT/AM",[TESTIF].[REMARKS]="AM",[TESTIF].[REMARKS]=[TESTIF].[REMARKS])));

Can any one tell me what I'm doing wrong or how to fix the
problem ?

Thanks

< I want to work in Theory - everything and everybody works in Theory !!>

 

[RickSpr]

You can not change data with a SELECT statement, nor with an IIf function. What you need is an UPDATE statement.

UPDATE TestIf
SET TestIf.Remarks = &quot;AM&quot;
WHERE TestIf.Remarks = &quot;APPT/AM&quot;

If you want to change APPT/AM to AM and also change APPT/PM to PM, you could use two separate UPDATE statements, or you could use nested IIf() functions in this manner:

UPDATE TestIf
SET TestIf.Remarks = IIf(TestIf.Remarks = &quot;APPT/AM&quot;, &quot;AM&quot;, IIf(TestIf.Remarks = &quot;APPT/PM&quot;, &quot;PM&quot;, TestIf.Remarks))

Understand, though, that it is not the IIf() that is updating the data, it's the SET clause. The IIf() just returns a value to the program, it doesn't store it in the database.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein