This will stump you

[buggered]

This will confuse:

I have an expression...

IIf(IsDate([dbo_ContactExtraVw]![X501_Date__c]) And [dbo_ContactExtraVw]![X501_Workshop__c]="Scheduled" Or IsDate([dbo_ContactExtraVw]![X501_Date__c]) And [dbo_ContactExtraVw]![X501_Workshop__c]="Invited",IIf(CDate([dbo_ContactExtraVw]![X501_Date__c]) Between DateSerial(Year(DateAdd("m",1,Date())),Month(DateAdd("m",1,Date())),1) And DateSerial(Year(DateAdd("m",1,Date())),Month(DateAdd("m",1,Date())),31),[dbo_ContactExtraVw]![X501_Date__c],""),"")

..which was working fine until today, when I went to add an additional field. Every time I try to save, access shuts down. Other queries work fine, the tables are fine etc. I think its something to do with references? has anyone ever had similar problems or have a clue how this can be resolved?
I use access 2002.

ps love the site, its helped me out many a time

 

[GrahamT]

Have you tried re-starting Access? What about re-booting your PC? Compacting and/or repairing the database? De-compiling and re-compiling the database? Copying the tables, queries, forms etc. etc. etc. to another new database? Or just simplifying or splitting the expression into more managable chunks?

(These suggestions are not in order of priority or any other order - just a list of possible things to try when things don't seem to make any sense at all.)

 

[buggered]

Yup, tried all that. It does seem that I need to split the query down into more managebale chunks which is a pain cause all that time coming up with a once working expression is wasted.

 

[GrahamT]

IIF statements take a lot of CPU time 'cos each part of the statement is evaluated, even if it isn't subsequently used. Nested IIFs are even worse. If (excuse the pun) you can replace them with some other construct, preferably external to SQL, it may help.

 

[neileg]

There are a couple of spurious spaces in your DateAdd statments.

 

[buggered]

The spaces are copy & paste errors.
The query runs fine & does not take too long (less than 1000 records being interrogated).
The problem is when I try to save any changes to this query, windows shuts down access. I'm able to save changes to other queries, just not this one. I've tried starting from scratch with a new d/b, copy the sql over etc. but still the problem occurs. Thats why I think its something to do with not having the right references ticked in visual basic. The most infuriating thing is that I was able to make changes & save, & then an hour later I wasn't. The only thing I did inbetween that I think could have had a knock on is I deleted an unused module.

 

[Pat Hartman]

Why not post the query that is causing the problem?

FYI, the IIf() returns a date or a zero length string. The two data types are incompatable. The ZLS's should be changed to Null.