Null Values In Query

[EDGE99]

I have two tables one is a transaction table and the other is a list products with special pricing discounts(SPD). Not all items in the transaction table will be listed in the SPD table.

In my query I do a calculation to determine the discount plus some other calculations along with the discount. However the items that are not found in the SPD table will be null. I need the value to be set to zero instead of null. I have tried the following and a few simular to it but I cannot get it to work.

Field in query:

test: IIf([tst_product]![discount]="",0,(do some calculation)

or

test: IIf([tst_product]![discount]="","null","not null")

But the query gets errors:

ProductNumber MasterProductNumber ProductDescription ListPrice Cost discount test
1 100 Widget Part 1 200 80 2 #Error
2 101 Widget Part 2 400 120 3 #Error
3 102 Widget Part 3 100 20 not null
4 100 Widget Part 4 300 50 2 #Error
5 103 Widget Part 5 500 100 not null
6 106 Widget Part 6 500 200 6 #Error
7 105 Widget Part 7 1000 200 5 #Error
8 106 Widget Part 8 1000 300 6 #Error
9 100 Widget Part 9 500 300 2 #Error
10 107 W7 1000 200 not null
11 108 W8 2000 300 not null


The check fails and I am not sure how I can change the null value to a zero. If anyone can let me know what I am doing wrong I would appreciate it.

Thanks in advance,

 

[EDGE99]

Nevermind I figured it out. I found the IsNull function and that cleared up the issue.

test: IIf(IsNull([tst_product]![discount]),"null","not null")

 

[PHV]

I need the value to be set to zero instead of null
Why not simply use the Nz function ?
Nz([tst_product]![discount],0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886