URGENT HELP - Expression Closing Perenthesis

[Guest]

Hi,
I really need help - this formula is ticking me off! Well, here's the
expression:

TotalBill: IIf([TotalIncurredDscnt] Is Not
Null,(([TotalIncurredNorm]+(IIf([TotalIncurredExt] Is Not
Null,[TotalIncurredExt],0)))-(IIF([TotalIncurredDscnt]Is Not
Null,[TotalIncurredDscnt],0)),([TotalIncurredNorm]+(IIf([TotalIncurredExt] Is
Not Null,[TotalIncurredExt],0)))

I know it looks like a mess, but logically, to me, it seems to work.
However, Access 2003 thinks otherwise!
After I enter the formula and try to move the cursor, Access shows the
following error message:

"The expression you entered is missing a closing perenthesis, bracket (]),
or vertical bar(│)."

I have no idea what it is saying, I mean the formula is bracketed properly,
as far as I know!! Please help!

Thank you for taking your time to help me. ;-)

 

[Tom Ellison]

Dear Sid:

Your expression has 10 left parens and 8 right parens by my count. What do
you get when you count them?

How to fix this depends on what you want this to do.

How about this:

TotalBill: Nz(TotalIncurredDscnt, 0) +
Nz(TotalIncurredExt, 0) -
Nz(TotalIncurredDscnt, 0) +
Nz(TotalIncurredNorm, 0)

Do you just want to use zero for these 4 things when they are null, adding
and subtracting as shown? Or do you want to do something else?

Tom Ellison

 

[John Vinson]

Hi,
I really need help - this formula is ticking me off! Well, here's the
expression:

You could really simplify this by using the NZ() function (which
returns zero or its optional second argument if the argument is NULL)
rather than all the nested IIF's. I'm not certain though just what you
want to accomplish! (and yes, I get mismatched parens as well; I like
to run my finger along the expression mentally adding 1 for each left
paren and subtracting 1 for each right; and I get 2 for your
expression).

John W. Vinson[MVP]

 

[Guest]

Thank you both very much. You're right, those nested If's did have parenthsis
mismatch. Thanks for the Nz function! Would have never come across that
otherwise!!

Thank you so much!

Dear Sid:

Your expression has 10 left parens and 8 right parens by my count. What do
you get when you count them?

How to fix this depends on what you want this to do.

How about this:

TotalBill: Nz(TotalIncurredDscnt, 0) +
Nz(TotalIncurredExt, 0) -
Nz(TotalIncurredDscnt, 0) +
Nz(TotalIncurredNorm, 0)

Do you just want to use zero for these 4 things when they are null, adding
and subtracting as shown? Or do you want to do something else?

Tom Ellison

Hi,
I really need help - this formula is ticking me off! Well, here's the
expression:

TotalBill: IIf([TotalIncurredDscnt] Is Not
Null,(([TotalIncurredNorm]+(IIf([TotalIncurredExt] Is Not
Null,[TotalIncurredExt],0)))-(IIF([TotalIncurredDscnt]Is Not
Null,[TotalIncurredDscnt],0)),([TotalIncurredNorm]+(IIf([TotalIncurredExt]
Is
Not Null,[TotalIncurredExt],0)))

I know it looks like a mess, but logically, to me, it seems to work.
However, Access 2003 thinks otherwise!
After I enter the formula and try to move the cursor, Access shows the
following error message:

"The expression you entered is missing a closing perenthesis, bracket (]),
or vertical bar(?)."

I have no idea what it is saying, I mean the formula is bracketed
properly,
as far as I know!! Please help!

Thank you for taking your time to help me. ;-)