Error calculating a sum expression

[jonman1124]

i have a field in a query with a field called current_LTV:

current_LTV: IIf([qfinancial]![most recent appraisal value] Is Null,[qfinancial]![Actual Balance]/[all]![appraisal value],[qfinancial]![Actual Balance]/[qfinancial]![most recent appraisal value])

and then in another query, i want to add it all up grouped by a certain field, but i'm getting this error:
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

 

[Norris68]

Try using the IsNull function instead of ...Is Null.

current_LTV: IIf(IsNull([qfinancial]![most recent appraisal value]),[qfinancial]![Actual Balance]/[all]![appraisal value],[qfinancial]![Actual Balance]/[qfinancial]![most recent appraisal value])

Where are you pulling the data from - tables or forms? (Strange []![] notation)

 

[jonman1124]

pulling the data from other queries. i thought []![] notation was standard...

 

[jonman1124]

i just tried what you suggested, but the second query summing the current_LTV field still produces the same error.

 

[Norris68]

I usually use dot notation myself e.g. qFinancial.[Actual Balance]. There is a thread or FAQ somewhere about the differences between dot and ! notation, but I can't find it for the life of me at the moment!

 

[Norris68]

You could try simplifying it a bit:

current_LTV: [qfinancial]![Actual Balance]/NZ([qfinancial]![most recent appraisal value],[all]![appraisal value])

Where is it failing? The inner query where current_LTV is calculated or the outer query where it is summed?

(Sorry BTW, but off home now - someone else will have to pick this up...)

 

[jonman1124]

i dunno. i just saw the ! notation before and i stuck with it. back to my problem... i did a maketable on the first field with the current_ltv field. this let my second query with the sum work. why is that? i dont really think my expression was that complex. is there a way to make it work without the maketable? i don't really awnt the data to be static

 

[jonman1124]

ok... it's the NZ portion of the expression that is killing me. if i remove that NZ portion it works without the maketable. leave it in, and it only works with the maketable and with very poor performance (long lag). what is the best way to use the NZ function or the IIF statement?