Database closes on save of query: how do I stop this happening

[geordiekev]

I've got a database, which has been working fine for the last 6 months, it's nt spectacularly large, or complex (abotu 10mb, 15 tables, (5 of which are lookups), about 20 forms, 40 reports, and 30 or so queries).

I've tried to add a new query today, again nothing special:

all comes from one table:
column 1 - group by
column 2 - max
column 3 - if = "No"
column 4 is an expression: iif (a=1,col_a,iif(a=2,col_b,iif(a=3,col_c, .... ))) and so on for 8 col_*'s

The query creates happily enough, and is viewable.
When I try to save it, access shuts down completely with no warnings.

creating the query apart from the last column, saving it then works, but on adding the final column the database shuts down every time.

any ideas? I'm at a loss on this.

Cheers
Kev

 

[KenHigg]

Hum... I would try removing the last iif() and see if it works. You may have reached some kind of limit...

???

 

[The Stoat]

Hi,

Access limits nested iif's to 7. It maybe that you have hit an unhandled error that's causing it to crash. Try - for the sake of experimentation - to reduce the nesting to 5 for example and see if this causes the same error.

TS