I want to see null values......

[PaulT]

Easy one for the experts here I hope:

I want query to show all results including any null values because at the moment it just misses the null values out.

So for example instead of the query saying that Fruiterer A has 5 apples, 0 oranges and 4 bananas it just says that Fruiterer A has 5 apples and 4 bananas. Therefore because oranges was a null value then it simply makes no reference to oranges.

Please help, many thanks,

Paul

 

[ColinEssex]

Use the NZ function then in the count query put the criteria >0

Col

 

[Bhaughbb]

Does the source table(s) with this information have a record that says oranges if it is 0 or is it skipped? If it has a record that says oranges but for quantity is blank you could always put an IF statement in the field something like:

FieldName: IIF(IsNull([FruitQuantity]),0,[FruitQuantity])

If the record doesn't exist at all if it is a null value then a cross tab query might be your best bet with sorting fields as the rows, fruit type as the column and the quantity as a value, if they are already total numbers the IIF statement would return your value of 0 again, otherwise you could use a sum or count statement depending on what is in the field.