Access Quits when Save Query

[cariengon]

Very strange thing happening...

I'm working in a query and creating a field. This worked fine when there were 3 Iif statements. I added 4 more. The finished field looks like this:

BCRT #: IIf([104_BCRT_Creditor_tbl]![BCRT_Report_Order] Is Null,"Under $1M",IIf([104_BCRT_Creditor_tbl]![BCRT_Report_Order]=60,"$0 Claim",IIf([104_BCRT_Creditor_tbl]![BCRT_Report_Order]=70,"Under $1M",IIf([104_BCRT_Creditor_tbl]![BCRT_Report_Order]=55,"Under $1M",IIf([104_BCRT_Creditor_tbl]![BCRT_Report_Order]=56,"Under $1M",IIf([104_BCRT_Creditor_tbl]![BCRT_Report_Order]=57,"Under $1M",IIf([104_BCRT_Creditor_tbl]![BCRT_Report_Order]=58,"Under $1M",[104_BCRT_Creditor_tbl]![BCRT_Creditor_Num])))))))

When I go to preview the results - it works like it is supposed to. When I go to save my changes, the whole Access Application quits.

Anybody know why this may be happening? I didn't know that there was a limit of IIF statements - if so, it definitely is not 7...

I'm using Access XP in Windows XP - if this makes a difference.

HELP!!

Thanks,
Carie

 

[flyover789]

I don't know if there is a limitation of if's in Jet-SQL, but you could try to reduce the if's by using some or's:

BCRT #: IIf([104_BCRT_Creditor_tbl]![BCRT_Report_Order] Is Null Or [104_BCRT_Creditor_tbl]![BCRT_Report_Order]=70 Or [104_BCRT_Creditor_tbl]![BCRT_Report_Order]=55 Or [104_BCRT_Creditor_tbl]![BCRT_Report_Order]=56 Or [104_BCRT_Creditor_tbl]![BCRT_Report_Order]=57 Or [104_BCRT_Creditor_tbl]![BCRT_Report_Order]=58,"Under $1M",IIf([104_BCRT_Creditor_tbl]![BCRT_Report_Order]=60,"$0 Claim",[104_BCRT_Creditor_tbl]![BCRT_Creditor_Num]))

even shorter with "between":

BCRT #: IIf([104_BCRT_Creditor_tbl]![BCRT_Report_Order] Is Null Or [104_BCRT_Creditor_tbl]![BCRT_Report_Order]=70 Or [104_BCRT_Creditor_tbl]![BCRT_Report_Order] Between 55 and 58,"Under $1M",IIf([104_BCRT_Creditor_tbl]![BCRT_Report_Order]=60,"$0 Claim",[104_BCRT_Creditor_tbl]![BCRT_Creditor_Num]))

HTH,
fly


Martin Serra Jr.

http://www.mserrasystems.de

 

[cariengon]

Thanks - It wasn't the length of the if statements. It was a corrupt database. Reverted to a backup database and was able to make the changes.

The most frustrating thing is how the heck did the database get corrupted???

Thanks -
Carie

 

[cariengon]

I found that this wasn't database corruption because it was only happening when I was working with Queries. What I did find is that the more complex the querie (ie - lot's of criteria) it happened. When I simplified my parameters, it worked much better and didn't crash on me.