query IIf problem

[seanyeap]

Dear All,
Based on the expression in query field,what if the statement is false, and i want it to return to "wrong" instead of "0"?

IIF([Authorized]=1 And [DayID] In (1,3,4), 100, IIF([Authorized]=1 And [DayID] In (2,5), 200, 0))

Thanks

Sean

 

[dcx693]

i want it to return to "wrong" instead of "0"?

Do you mean you want it to return the string "wrong"? If so:

IIF([Authorized]=1 And [DayID] In (1,3,4), 100, IIF([Authorized]=1 And [DayID] In (2,5), 200, "wrong"))

 

[seanyeap]

Dear dcx693,
I have an error "Data type mismatch in critiria expression"

Regards,
Sean

 

[GGib7711]

You can't put the word "Wrong" in a numeric field.

 

[Dugantrain]

Put single quotes around your '100' so that your expression will only return strings.

 

[dcx693]

You can't put the word "Wrong" in a numeric field

True, but what if the field is a calculated field in a query (and thus does not have an assigned data type)? That's what I assumed.

Put single quotes around your '100' so that your expression will only return strings.

That will work (I tested a similar expression), but it won't produce the desired result.

Just as a test, I typed:
? IIf(True And False,100,IIf(True And False,200,"wrong"))
into the immediate window just to test the expression itself out, and it worked. I was able to get it to print out the "wrong" string. I then placed it into a query grid and tried to run the query. I got "#Error".

I'm not sure what's going on here, but it seems that if the result of the first IIf condition is true, and the "true" result is a string, then the false condition of the 2nd IIf can be a string OR a number - and it works in the QBE grid as a calculated field. But if the true result of the first IIf is a number, then the false result of the second IIf can only be a number.

Just to test this theory, I rearranged the expression to be this:
IIf([Authorized]<>1 Or [DayID] Not In (1,2,3,4,5),"wrong",IIf([Authorized]=1 And [DayID] In (1,3,4),100,200))
Not totally sure it works correctly, but it seems to using just a few test cases I threw at it.

Please let me know what you all think.

 

[GGib7711]

Do you mean you want it to return the string "wrong"? If so:

IIF([Authorized]=1 And [DayID] In (1,3,4), 100, IIF([Authorized]=1 And [DayID] In (2,5), 200, "wrong"))

Do you mean we can put

100   <- - a number, not text.
200   <- - a number, not text.
wrong <- - text.

in one column (that is, in the same field) in the query results?

 

[dcx693]

Do you mean we can put

100   <- - a number, not text.
200   <- - a number, not text.
wrong <- - text.

in one column (that is, in the same field) in the query results?

In a calculated field, yes. The rewrite of the IIf formula example at the bottom of my last post does it.

 

[GGib7711]

In the calculated field, the numbers become text!

If the results are not used in further calculations, then it is quite OK.