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?
I took a quick look but maybe this is an easy answer. Try this:
In your query, you might use this:
Now, any null value returns 0. The problem in queries is that you can't add a null value to a numeric entry. So null+1 is not equal to 1, it's equal to null. But by using NZ(), you get a 0+1=1.
Of course, you have to decide what the null value should return. For example, if you are looking for a True/False value, you would return False. If you are adding strings (like First Name+Last Name) you would use a blank string value, "".
Null values, when used in conjunction with other query criteria, can tend to cause the entire record to disappear from view.
A few other things to consider in calculating queries I have found helpful:
1. Some calculations may return an error (for example if division by 0 occurs). To test for this you can use IIF(IsError(),True, False) in the query calculation to ensure you don't end up with #Error values in your query results.
2. You can also use IIF() to check for all sorts of situations before allowing a calculation to occur. In other words, if you find a certain result occurs using IIF() that you test for, then you could return a different value to the query field so you don't get poor data in the results.
3. Using IIF, NZ, and other additional tests in query calculations causes a performance hit. I would experiment with different combinations of queries (or nested queries) so that you don't have to test large sets of data at any one time.
4. You may also (in your example) want to experiment using left and right joins. That is, double-click on one of the join lines from the parent table to the child table, and choose to include all items from the parent. This way, any parent records that have no children do not get knocked out of appearing in the query results. By including all records from the parent, you will always show the full table.
Maybe some other readers would want to add their thoughts on your query arrangement.