Problem with textboxes and calculations

[Zergal]

Hello!



I am currently working on an Access 2007 form. This form will be used for taking orders. It then will be printed or saved as PDF. The form gets its information from queries, that are based on live links with few excell spreadsheets. The criteria for the queries are set by the fields on a form. For example, after selecting the customer, one query will search for all avaiable products for that customer. After selecting a product, another query gets the Pack size. To get the "SP - £/kg" and "BP - £/kg" I used a query that uses "Product", "Customer" and "Total (amount of product)" as its criteria.



My problem is with textboxes for "SP - £/kg" and "BP - £/kg". They all use Dlookup function to get their values from the query - It works fine.
However if, lets say, only 1 product is selected on the form, the other 5 rows stay unpopulated. This makes the other 5 "SP - £/kg" and "BP - £/kg" rows stay empty. Unfortnately, this meses up calculations for "Profit" and "Total Selling Price" I managed to identify the problem, that is If I use empty textboxes in calculations, I just get empty fields for results.

My question is, is it possible to make the "SP - £/kg" and "BP - £/kg" fields to be 0 (or at least used as 0 in calculations) instead of being empty? I tried setting the default value to 0, that didn't work. I attempted to use Iff function but I failed miserably. I couldn't find anything helpful on the internet neither. The text boxes are set to 'currency' format. Perhaps there is a solution in the calculations?

If everything else fails, I will just use List Boxes instead of textboxes with Dlookup. Problem with list boxes is that you have to first select the value (note there is only 1 to select), I want to avoid that.

Any help will be greatly appreciated !

[Rawb]

Try Nz(<Your DLookup Here>,0)

[Zergal]

Thank you, it worked! I saw the Nz thing before on few other websites but it looked very complicated so I didn't have a clue on how to apply it .