NZ In Query

D

DS

I have an Expression in a Query that isn't working. Can anyone see why?



Expr2: Nz([DiscountAmount],CCur([Expr1]-[DiscountAmount]),[Expr1])

If DiscountAmount is Null then its Expr1
Otherwise it's Expr1-DiscountAmount

Thanks
DS
 
D

DS

DS said:
I have an Expression in a Query that isn't working. Can anyone see why?



Expr2: Nz([DiscountAmount],CCur([Expr1]-[DiscountAmount]),[Expr1])

If DiscountAmount is Null then its Expr1
Otherwise it's Expr1-DiscountAmount

Thanks
DS
Click to expand...

Of Course, the minute I post it I figure it out!!!!

Expr2: CCur(Nz([Expr1]-[DiscountAmount],[Expr1]))

Sorry!
DS
 
O

Ofer

You use the NZ as IIf, with two values, when NZ has only one value when null
Use that instead
IIF(isnull([DiscountAmount]),[Expr1],CCur([Expr1]-[DiscountAmount]))
 
G

George Nicholson

Expr2: CCur(Nz([Expr1]-[DiscountAmount],[Expr1]))

or, more to the point:

Ccur([Expr1] - nz([DiscountAmount],0))
Limiting the Null test to DiscountAmount since you are apparently not
worried if Expr1 is Null.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


DS said:
DS said:
I have an Expression in a Query that isn't working. Can anyone see why?



Expr2: Nz([DiscountAmount],CCur([Expr1]-[DiscountAmount]),[Expr1])

If DiscountAmount is Null then its Expr1
Otherwise it's Expr1-DiscountAmount

Thanks
DS
Click to expand...

Of Course, the minute I post it I figure it out!!!!

Expr2: CCur(Nz([Expr1]-[DiscountAmount],[Expr1]))

Sorry!
DS
Click to expand...
 
D

DS

Ofer said:
You use the NZ as IIf, with two values, when NZ has only one value when null
Use that instead
IIF(isnull([DiscountAmount]),[Expr1],CCur([Expr1]-[DiscountAmount]))
:

I have an Expression in a Query that isn't working. Can anyone see why?



Expr2: Nz([DiscountAmount],CCur([Expr1]-[DiscountAmount]),[Expr1])

If DiscountAmount is Null then its Expr1
Otherwise it's Expr1-DiscountAmount

Thanks
DS
Click to expand...
Click to expand...
Thank You
DS
 
Top