Iif statement in a query

[DC CS]

Hi all,


I have the below statement in an Access Query, but I receive an error message. I am not sure what causes the error as I am sure the statement syntax is correct:





Thanks

[June7]

Don't see anything wrong with expression.
Post the actual SQL statement.

Suggest you use a more informative alias field name than "Expr1"

[Micron]

There is something wrong with that picture. Access would capitalize Iif as IIf.
You could try removing the calculated field and see if it runs at all. I suspect the issue is somewhere else - especially if you wrote/copied the sql. IIRC, the message is often due to missing separators of one kind or another.

[Welshgasman]

Is that actually a lowercase L? lif

[Micron]

Is that actually a lowercase L? lif

If it is, it would raise 'undefined function' error, unless of course you had such a function within scope of the query.

[DC CS]

See the updated Syntax. Once I press Enter, I receive the message and the "Yes" is highlighted. The "iif" has not yet been capatilized by Access.
Access is not allowing me to view the SQL statement. As soon as I move the focus from the "Expr1" field, the error message is displayed.
Is it possible that my Access app may be corrupted?

[Welshgasman]

Well copy what you need to test to a new DB and see if it works then?
Failing that you mght need to upload enough for us to try and test ourselves.

[DC CS]

I have copied to a new DB with the same results. I also have repaired my Office 365 apps, still the same.

The DB is structured as follows (not sure if I can upload the demo DB for you to use to test).

1. A table with 2 fields (MyMonth as Date/Time and Quantity as number field)
2. Query that shows the above 2 fields
3. Two expression fields in the query that checks the following:
i) If the Quantity field = 1 then display "Yes" else display "No"
ii) If the Quantity minus 3 (Quantity - 3) = 1 then display "1" else display "0"

I have done these types of expressions with IIF in queries in other databases with no issues. This is the first time that I am encountering this.

Thanks

[Welshgasman]

I will not be able to touch a 365 DB, so I am out.

See attachments on your reply for uploading DB.

[Minty]

The syntax looks correct, so either the quotes aren't normal double quotes (It's difficult to tell in that font) or your regional settings separators shouldn't be comma's .

If all of the above isn't the cause you'll have to upload a sample demonstrating the issue.
Remove any sensitive data, compact and repair, then zip the file and attach it here.

[DC CS]

Hi,
I have changed the Iif statement and is now giving me the desired results. It seems to be the data type of the field that causes the error. I attach the solved expression..

[Welshgasman]

You are telling us quantity is text?
If you are using Val() to convert to a numeric, why are you comparing against text?

[DC CS]

The Quantity field is NUMBER. I attach the demo DB, maybe it will help.

Database2.zip

[CJ_London]

your number is a double and doubles are not precise. Assuming quantity cannot be fractional (e.g. 1.5) then change the number type to integer or long. If you need decimal precision use the currency datatype. Or use the round function instead of val

[DC CS]

Hi CJ,
I changed the number type to Integer, Long and Decimal, but with the same results!