Query

[Jeff_Ford]

A query I have created needs to sum fields

“QuantityInGood”-“QuantityOutGood”

And

“QuantityInBad”-“QuantityOutBad”

The information for these fields come from two sub queries

1. qryrptsubQuantityInStockBySupplier
2. qryrptsubQuantityInQuarantineBySupplier

The information for these sub quries come from entries into two fields within a table, tblStock (QBookedIn, QBookedOut) Dependentant upon the entry in field Quanrantined, 0=Good and 1=Bad

The sub queries use 0 and 1 as a “where” statement to display the following fields within the final query

QuantityInGood, QuantityOutGood, QuantityInBad, QuantityOutBad in the final Query.

The problem is that two of these fields (Dependant upon 0 or 1) will contain null’s and the query returns no results for that product ID. As more results are entered into the table eventually all fields will contain values and the query performs properly.

What is the answer to this problem with dealing with the nulls?

 

[charityg]

Try using
TotalGood:Nz(BookedInGood,0)-Nz(BookedoutGood,0)

Nz is a function that converts Null to whatever you want, in the above case, I'm converting null to 0

 

[WayneRyan]

Jeff,

If the problem is that in certain individual records there are
nulls, then you can use the Iif function:

NewVal: Iif(IsNull([SomeField]), 0, [SomeField])

Then use NewVal in your subsequent queries.

Wayne