I came across an odd thing today. I have a database with a numeric field and a Yes/No field. If the Yes/No is Yes, a calculation is done on the numeric field (in a query), otherwise a different calculation is carried out.
Lets say the numeric field is “Hours” and the Yes/No is “Extra”, and the calculation is to multiply Hours by 0 (if Yes) or -1 (if No). This may not make sense here but does in the context of my database.
Anyway. Unlike Excel or VB or pretty much anything, Access uses an If statement called “Iif”. With two eyes. According to the Access Help, you use it like this:
=IIf([Confirmed] = "Yes", "Order Confirmed", "Order Not Confirmed")
This suggests you match the word Yes from the Yes/No field, right? Wrong.
If I do this:
=IIf([Extra] = "Yes", 0, -1)
Then both Yes and No match to give -1. After checking more documentation, and searching round the internets, virtually all examples seem to check against the words Yes or No. Which doesn’t work.
The solution (as I finally twigged) is to do this:
=IIf([Extra] = True, 0, -1)
Now why can’t the examples tell you this?
Hey Thanks a lot, what you figured out helped me out also. I had the same question. I understand now why the “yes” did not work. It is because the yes/no function is a logical variable, to use programming terms, so it only sees yes and no as true or false.
Peace
Chad
You are AWESOME! I couldn’t even find this in my Access 2007 book, the comprehensive version. Thank you soooo much!
OMG thank you so much lifesaver!