Simple IIF question....

[TjS]

Hellow,

I must have done something wrong. I get an #Error when using the following statement in a textbox on a MS_Access report:

IIF([problem]=1;"Yes";"no")

The field [problem] consists of three values, resp. 1 (Yes) and 2 (No) and 0 (maybe)

(be aware, it is not a boolean)

Please advise on correcting my IIF statement..

 

[neileg]

So far as I am aware you sould be using commas not semi-colons plus you need an equals sign

=IIF([problem]=1,"Yes","no")

Final thought is that if [problem] happens to be a text field and not a number, you need quotes

=IIF([problem]="1","Yes","no")

 

[TjS]

Thanks for the answer

When i don't use semi-colons the error "INvalid Syntax" appears.
I noticed the difference between writing a SQL-query (no semi-colons) and writing an expression in the controlsource of a textbox (using semi-colons otherwise the expression isn't accepted).

I tried your solutions, but unfortunately all i get is "#Error...";
the fieldname is also correct (no doubt here

weird...

 

[neileg]

Works fine for me. See the attached.

 

[TjS]

Thanks

Thanks Neil,

I tried a new report with just the field from the table (my database) and that worked fine as well. But when i put the same expression in the report i want the expression to work in, it still says "error"; also tried different combinations...no luck.. (going crazy here)...

 

[neileg]

Couple of things to check.

1) the field you are referencing in the formula does exist in the dataset on which the form is based
2) If controls on the report have the same names as the underlying fields, this can cause problems. I know the Access wizards do this by default, but it's bad practice. Try renaming your controls.

 

[TjS]

euhm....
1) yep; does exists
2) renamed the textbox

Maybe i have to tell you that it is not about a form, but a report in which i put a textbox which displays the result (should work the same as the form though).

Still figuring here, but hey What's life without puzzles?

 

[TjS]

Yes !!

It works!
Somewhere in the line (after renaming the label/textbox) the fieldname was renamed as well. I renamed the fieldname to the original, and now it works!

If you were a girl i would kiss you!

Thanks Neil..see you 'round.

 

[neileg]

If you were a girl i would kiss you!

I was a girl I might let you