Access 2007 "The Query Could Not Be Executed"

[tehNellie]

Anyone able to shed any light on what this error message actually means (beyond the obvious?).

I have a couple of queries written in an identical manner. One runs fine always, the others sometimes run but mostly return the above error message and I'm at a loss to explain why. The data types are the same, the SQL is the same only the underlying table changes.

My suspicion is that it's related to a couple of iif statements I use to validate data but I can't see a consistent issue around these. I did wonder whether evaluating True/False when Null was involved might be it, but masking NULL to 0 makes no difference. In all queries, the IIF statements are based on the same internal tables so the data formats are consistent. All the IIF statements do is return YES/NO based on the value of three of the ID columns. Ie

iif(isNull(columnname),No,Yes) as Check1
iif((isNUll(columnName2) or ColumnName2>0),Yes,No)

For compatibility reasons the database itself is a 2000 format .mdb

The final query is based on a 3 step query (on a query on a query). Actually that's not quite true, the query that always works is the 5th query as the initial "Table" is actually a UNION query combining three related datasets for comparison.

Any Suggestions welcome.

 

[tehNellie]

Well I thought I'd fixed it by reworking the IIF statements somewhat but it was just lulling me into a false sense of security. Again the series that always work continue to work, the others work for a while and then stop.

Have noticed that when it stops working that I can just open the query in SQL view, change any single character and then the query will work again. I.e I can delete a character and then put it back in again and the query will then work again for a while.