IIF AND function in query field

[Kregg]

I am attempted to create an IIF AND function in a query field. I have the following expression but for some reason the AND will not work.

Expr3: IIf([EXPR1]=1 And [EMPLOYED]="YES","YES",IIf([EXPR1]=1 And [EMPLOYED]="NO","NO",IIf([EXPR1]=0,"NA")))

The last section {IIF([EXPR1]=0,"NA")} works but any part of the expression that has "And" returns a #error message.

Any ideas on how I can correct this issue? Thanks!

I am also not apposed to a switch( solution to this problem as I have other IIF functions that are needed but I am running in to the same issue with the switch( function as well.

 

[Cronk]

There's no Else part in the last IIf

 

[Brianwarnock]

If expr1 can only equal 1 or0 then

Iif(expr1=1,Iif(employed="YES","YES","NO"),N/A)

Brian

 

[Kregg]

There's no Else part in the last IIf

I have tried this with the following but errors are still returned for any expression that has an "and" operator.

Expr3: IIf([EXPR1]=1 And [EMPLOYED]="YES","YES",IIf([EXPR1]=1 And [EMPLOYED]="NO","NO",IIf([EXPR1]=0,"NA","NA")))

 

[Kregg]

If expr1 can only equal 1 or0 then

Iif(expr1=1,Iif(employed="YES","YES","NO"),N/A)

Brian

Thanks for the response. I have another expression that will require an "and" so I would appreciate if there is a solution that can include the "and" operator.

 

[JHB]

What data type is [EMPLOYED]? Text type or "Yes/No"?
If "Yes/No" then:

Expr3: IIf([EXPR1]=1 And [EMPLOYED]=-1,"YES",IIf([EXPR1]=1 And [EMPLOYED]=0,"NO",IIf([EXPR1]=0,"NA","NA")))

 

[Cronk]

Actually, Brian's expression is good (apart from missing quotes around the last N/A). It is more efficient because it avoids the AND.

NB the last IIf gives "N/A" in both cases.

I find it hard to debug complex IIf expressions and if confronted with problems, I'd replace it with a query where the logic can be followed more easily.

I can't see anything wrong with the formula, other than Kregg's suggestion about field types.

 

[Kregg]

What data type is [EMPLOYED]? Text type or "Yes/No"?
If "Yes/No" then:

The fields are yes/no so I will try the =1 and =0 that you suggested today.

Thanks for the idea.

 

[Kregg]

Actually, Brian's expression is good (apart from missing quotes around the last N/A). It is more efficient because it avoids the AND.

NB the last IIf gives "N/A" in both cases.

I find it hard to debug complex IIf expressions and if confronted with problems, I'd replace it with a query where the logic can be followed more easily.

I can't see anything wrong with the formula, other than Kregg's suggestion about field types.

I agree that Brian's expression is the most efficient for the expression that I posted. I unfortunately picked the simpler issue hoping I could then translate it into the more complex expression.

The complex expression is as follows:

Iif([TRAINING STATUS]="COMPLETED" and [CREDENTIAL ATTAINED]>[REGISTRATIONDATE],"YES",iif([TRAINING STATUS]="COMPLETED" and [WORKKEYSCOMPLETED]>[REGISTRATIONDATE],"YES","NO"))

[TRAINING STATUS] is a list box with the following values: COMPLETED; NOT COMPLETED (there are more options but these two are only relavent to the current query)
[CREDENTIAL ATTAINED], [WORKKEYSCOMPLETED] and [REGISTRATIONDATE] are all Date/Time fields

My assumption is that the expression above when working should return "NO" whenever [TRAINING STATUS] is "NOT COMPLETED", is this correct?

This expression gives me errors whenever the "and" operator is used but if I remove the "and" operator each expression works fine on there own....

 

[Cronk]

You're providing a moving target when you post something other than the real formula.

There is no problem using AND in an IIf statement.

You have not responded to whether the field types in the comparisons are the same. For the record, what are they?

 

[Kregg]

You're providing a moving target when you post something other than the real formula.

There is no problem using AND in an IIf statement.

You have not responded to whether the field types in the comparisons are the same. For the record, what are they?

I apologize for the curve ball. When I arrived at the office I tried using -1 and 0 for YES/NO fields and that fixed the issue.

I am not sure what you mean by "the field types in the comparison are the same." I am new to access and to forums so I am a little behind on the terminology.

 

[Brianwarnock]

Using -1 and0 is confusing, I am sure that you can use TRUE FALSE. No quotes I even think that you maybe able to use YES NO. Without quotes but it is a while so I may be wrong.

It was a good catch by JHB

Brian