Don't see anything wrong with expression.
Post the actual SQL statement.
Suggest you use a more informative alias field name than "Expr1"
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Is that actually a lowercase L? lif
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
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?
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.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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
I will not be able to touch a 365 DB, so I am out.
See attachments on your reply for uploading DB.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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.
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
You are telling us quantity is text?
If you are using Val() to convert to a numeric, why are you comparing against text?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
The Quantity field is NUMBER. I attach the demo DB, maybe it will help.
Database2.zip
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
Hi CJ,
I changed the number type to Integer, Long and Decimal, but with the same results!