Create a new field with iif statment in a query

[justair07]

Hello,



Can someone please help me. I want to create a new field in my query call "Conforming" and another field called "Non-Conforming".

For "Conforming" I would like to have an iif statement like so (forgive my pitiful excuse for pseudocode I'm not very good with VBA)

iif Field 1 = 1, 3 or 4 And
Field 2 = 1, 3 or 4 And
Field 3 = 1, 3 or 4 And
Field 4 = 1, 3 or 4 And
etc.....
Then
Conforming = 1


For the "Non-Conforming" field I would like:

iif Field 1 = 2 OR
Field 2 = 2 OR
Field 3 = 2 OR
Field 4 = 2 OR
etc....
Then
Non-Conforming = 1

There may be a way to just include the 2nd outcome into the first code as an else statement:

iif Field 1 = 1, 3 or 4 And
Field 2 = 1, 3 or 4 And
Field 3 = 1, 3 or 4 And
Field 4 = 1, 3 or 4 And
etc.....
Then
Conforming = 1
Else
Non-Conforming = 1


But I'm not sure, like I said I suck at VBA. The may even be another way to do this without code. I have know Idea.

Can someone help me out?

[pbaldy]

You're right there. Building on your example:

iif(Field 1 = 2 OR Field 2 = 2 OR Field 3 = 2 OR Field 4 = 2, "Non Conforming", Conforming")

If your fields actually have spaces, they would have to be bracketed of course.

[justair07]

Awesome! Where would I add this? In the criteria?

[JoeM]

Where would I add this? In the criteria?

You would put it in a calculated field in your query, i.e.

STATUS: iif(Field 1 = 2 OR Field 2 = 2 OR Field 3 = 2 OR Field 4 = 2, "Non Conforming", Conforming")

See: http://office.microsoft.com/en-us/ac...aspx?section=3