IIF syntax

[batwings]

Hi there

I'm trying to write a query using an IIF statement in the "Field" property of the Access Query Design Grid the new Column is called F:

there are 3 Columns, System, Etot, Itot in a mytable

Basically in English the query goes,

IF mytable.[System] = 700,
mytable.[Etot] = 0,
mytable.[Itot] = 0
then the newly created derived column
mytable.[F] "N/A"
OTHERWISE
mytable.[F] "OK"

I hope someone understands,

Batwings

 

[neileg]

F: IIF((mytable.[System] = 700) AND (mytable.[Etot] = 0) AND (mytable.[Itot] = 0), "N/A","OK")

 

[MStef]

Look at "DemoIIfSintaxA2000.mdb", Query1.

 

[batwings]

many thanks to you both neileg and MStef, I just couldn't get the syntax right before!!

 

[batwings]

MStef / neileg or anyone else out there

I think I messed up here in my original post,

I should have said that there are 3 scenarios that I am trying to report on but only 2 outcomes.

If mytable[system]=700
(AND mytable[Etot]=0 AND mytable[Itot]=0)Then "N/A"

If mytable[system]=700
(AND mytable[Etot]=0 AND mytable[Itot]>0)Then "OK"

If mytable[system]=700
(AND mytable[Etot]>0 AND mytable[Itot]=0)Then "OK"

I'm just not sure how to nest these

Batwings

 

[MStef]

Hello batwings!

You really have 2 scenarios,
first scenario 700, 0, 0 = "N/A"
second scenario ALL other = "OK".
Try it in my Demo and see.

 

[neileg]

Yes, MStef is correct.