NZ In Query

[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

 

[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

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

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

Sorry!
DS

 

[Guest]

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]))

 

[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.

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

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

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

Sorry!
DS

 

[DS]

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

Thank You
DS