Problem with null values using Cross tab queries

[T1986]

Hello,



I was hoping someone could help:

I have a database that each day reports output by actual batches, planned batches and hopefully incomplete batches.
The data is imported from an excel spreadsheet on which each row represents a different batch (each with a specified volume). Each batch will have a planned and actual output day, from this I use crosstab queries to count and calculate total volume of batches planned and actually produced on each day.

I am trying to also report batches that have only been partially completed, so I have added another column to the excel sheet and created another crosstab query. Importantly, batches are not necessarily ever incomplete (the report runs once a day and most batches are started and finished in one day) so there is not a date in this field for every batch.
When I try to report this data in conjunction with the planned and actual output, it only reports for records where there is a date in the ‘Incomplete’ field. I’ve tried using isnull and nz functions but without success.

I haven’t included any examples as I think this is probably more of an organisational problem but can paste some samples if necessary.
Thanks in advance!

Tom

[ItsMe]

I use Booleans (Yes/No fields) in my tables to aid in data validation/verification during import procedures.