How do you skip over null values 1

[RBE]

Below is a formula that is in an unbound textbox on a form. The form has all the fields listed here,on it. The fileds are bound to tables as follows
purchase price = tenders table
gallons received= "" ""
inventory unit cost = "" ""
pipeline tariff to be accrued = tariffgl table
terminaling = terminalinggl table
terminaling income = termincgl table
not all tables are populated which means I get back null values in the form. In my test case the terminaling field is null. This formula will not calculate with a null field. How can I get results without populating that field. By the way all these are being pulled into the form via SQL and are being updated to the various tables on exit of the form. That is why I do not want to enter a default value into the control. I don't want a bunch of 0s on my tables.

=[PURCHASE PRICE]+((-[PIPELINE TARIFF TO BE ACCRUED]-[TERMINALING]-[TERMINALING INCOME FOR PFC TANK])/[GALLONS RECEIVED])-[INVENTORY UNIT COST]

 

[sko]

Use the Nz function. Here's a snippet from the help file.

Remarks

The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non-Null value even when it contains a Null value, use the Nz function to return a zero, a zero-length string, or a custom return value.

For example, the expression 2 + varX will always return a Null value when the Variant varX is Null. However, 2 + Nz(varX) returns 2.

 

[tomschafer]

Try using iif's in your formula...
=IIF([PURCHASE PRICE] is null, 0, [PURCHASE PRICE])+((-IIF([PIPELINE TARIFF TO BE ACCRUED] is null, 0, [PIPELINE TARIFF TO BE ACCRUED])-IIF([TERMINALING] is null, 0, [TERMINALING])-IIF([TERMINALING INCOME FOR PFC TANK] is null, 0, [TERMINALING INCOME FOR PFC TANK]))/IIF([GALLONS RECEIVED] is null, 0, [GALLONS RECEIVED]))-IIF([INVENTORY UNIT COST] is null, 0, [INVENTORY UNIT COST])

 

[RBE]

great tip sko that worked thanks

 

[tlbroadbent]

Using the NZ function is much simpler than IIF in a query like this.

=NZ([PURCHASE PRICE],0)+((-NZ([PIPELINE TARIFF TO BE ACCRUED],0)-NZ([TERMINALING],0)-NZ([TERMINALING INCOME FOR PFC TANK],0)/[GALLONS RECEIVED])-NZ([INVENTORY UNIT COST],0) Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.