Query which causes Access to unexpectedly close

[ashwah]

Hi,

I have a problem query which is run against call data in my call centre. The data contains fields like 'TIME', 'DATE', 'BTID' (ID showing area) and 'Answering location' (Shows the telephone exchange the call came in on). My query groups calls on an expression based on the BTID and 'Answering Location' fields. Sadly, when I edit this query, it rapidly shuts down Access (I guess it counts as crashing as the .ldb file stays). I'm pretty sure its the expression which is causing this as similar queries with a different expression don't cause the same problem. The expression is:

Answering Location: IIf([ERD_Daily_Download_0706]![Answering Location] Not Like "NTH*" And [ERD_Daily_Download_0706]![Answering Location] Not Like "RED*" And [ERD_Daily_Download_0706]![Answering Location] Not Like "NEWP*","OTHER",IIf([ERD_Daily_Download_0706]![Answering Location]<>"NEWPORT",[ERD_Daily_Download_0706]![Answering Location],IIf([ERD_Daily_Download_0706]![BTID]="MOBILE","NEWP_MOB","NEWP_LAND")))

If anyone has any suggestions I would really appreciate them. I've tried all the normal stuff of compacting/repairing, rebuilding the query elsewhere, etc, but to no avail.

Thanks

Ashwah

 

[cheerio]

You have 3 NOT LIKE clauses and that may be stretching Access.

You can get rid of two of them if you rewrite the expression to begin like this:

IIf(LEFT([ERD_Daily_Download_0706]![Answering Location],3) NOT  IN ("NTH", "RED" ) 
And 
[ERD_Daily_Download_0706]![Answering Location] Not Like "NEWP*",
"OTHER",