IIF Statement Not Working 1

[houstonbill]

I know that there is probably a simple explanation and I am doing something very stupid, but I can't see it. My IIF statement reads as indicated in the SQL below and it will not change the only entry possible in the Elect field (TWO) to YES. What am I doing wrong.

SELECT [ElectronicWS CSR Rpt Quality].FEX, [ElectronicWS CSR Rpt Quality].FDT, [ElectronicWS CSR Rpt Quality].CCN, [ElectronicWS CSR Rpt Quality].SUBNOFUL, [ElectronicWS CSR Rpt Quality].GROUPNO, [ElectronicWS CSR Rpt Quality].ICN, [ElectronicWS CSR Rpt Quality].ExamID, IIf([Elec]="TWO","YES","NO") AS Elect, [ElectronicWS CSR Rpt Quality].Name
FROM [ElectronicWS CSR Rpt Quality]
WHERE ((([ElectronicWS CSR Rpt Quality].FDT) Between [Begin Date] And [End Date]));

 

[drtree]

Hi,

Is it possible there are some spaces after the word "TWO" in your expression?
What's the data in the table look like? If one of my IIF statements doesn't work, that's usually where I start. Check out the data, see what the difference is, and go back and edit the data/formula as needed.

HTH,

Doc Tree

 

[dhookom]

First, you don't provide any details on your results and why you think they are wrong...

You state the Elect field but your expression IIf([Elec]="TWO","YES","NO") AS Elect references a field named Elec.

BTW: you have a field named name which may cause issues when Access doesn't know whether you are referring to the field named name or an object name.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[houstonbill]

Did I not say it was something really dumb that I just couldn't see. I needed another set of eyes. Thanks Duane, as the {Elec} should have been [Elect]!! I made the change and at first got a circular reference error but immediately corrected that and it works like a charm.

Thanks for the use of your eyes!

 

[genomon]

In fact "name" is on the list of reserved words and should be avoided altogether:

http://support.microsoft.com/kb/286335

The early bird may get the worm, but the second mouse gets the cheese in the trap.