Access Crash on Query Save. What do I do?

[LP in TX]

Access 2000- I have a query with in Access that has multiple tables linked
(4) with a compound if statement in a criteria feild. Acess will run the
query. When i try to add an additional iif() statement to this criterion
feild and then save (via Save As, Save Icon, or close window and then
warnings message pops up to save) the entire Access aplication crashes. It
will run this query though. Can anyone tell me what i am doing wrong or is
this a known issue (with work arounds?)?
Compound If statement-
Answer: IIf(([REVIEW ACCTS]![CE%]+[REVIEW ACCTS]![EQT%]+[REVIEW
ACCTS]![FI%]+[REVIEW ACCTS]![ALT%]+[REVIEW ACCTS]![RE%])=0,"0",IIf(([REVIEW
ACCTS]![EQT%]/([REVIEW ACCTS]![CE%]+[REVIEW ACCTS]![EQT%]+[REVIEW
ACCTS]![FI%]+[REVIEW ACCTS]![ALT%]+[REVIEW ACCTS]![RE%]))<[Code - Investment
Objective]![EQTRANGEHI],"When specialty (alternative) asset is removed from
allocation, account meets asset allocation criteria and guidlines",IIf([Code
- Investment Objective]![INVESTMENT OBJECTIVE] Like "*clos*","Account is
coded as Closing","x")))
Trying to add in an additional iif() statement for "x" at the end of
this string crashes upon save. Furthermore, trying to save the existing
query with this compound if statement crashes the entire application.

Please help...

 

[John Vinson]

On Thu, 18 Aug 2005 11:00:03 -0700, "LP in TX" <LP in

Access 2000- I have a query with in Access that has multiple tables linked
(4) with a compound if statement in a criteria feild. Acess will run the
query. When i try to add an additional iif() statement to this criterion
feild and then save (via Save As, Save Icon, or close window and then
warnings message pops up to save) the entire Access aplication crashes. It
will run this query though. Can anyone tell me what i am doing wrong or is
this a known issue (with work arounds?)?
Compound If statement-
Answer: IIf(([REVIEW ACCTS]![CE%]+[REVIEW ACCTS]![EQT%]+[REVIEW
ACCTS]![FI%]+[REVIEW ACCTS]![ALT%]+[REVIEW ACCTS]![RE%])=0,"0",IIf(([REVIEW
ACCTS]![EQT%]/([REVIEW ACCTS]![CE%]+[REVIEW ACCTS]![EQT%]+[REVIEW
ACCTS]![FI%]+[REVIEW ACCTS]![ALT%]+[REVIEW ACCTS]![RE%]))<[Code - Investment
Objective]![EQTRANGEHI],"When specialty (alternative) asset is removed from
allocation, account meets asset allocation criteria and guidlines",IIf([Code
- Investment Objective]![INVESTMENT OBJECTIVE] Like "*clos*","Account is
coded as Closing","x")))
Trying to add in an additional iif() statement for "x" at the end of
this string crashes upon save. Furthermore, trying to save the existing
query with this compound if statement crashes the entire application.

Please help...

The expression may just be too long: there is a limit on the length in
bytes of an expression.

You might want to use the simpler (for multiple layers) Switch()
function instead of IIF. See the VBA online help for Switch).

Also, it may be better to use . as a delimiter rather than ! if
[REVIEW ACCTS] is a table or fieldname. In fact, if the fieldnames CE%
and so on are unique within the query, dispense with the [REVIEW
ACCTS] entirely, it's not necessary. This may simplify the expression
enough to let you get by.

John W. Vinson[MVP]