Ignore queries with missing fields when union query runs

[wvandenberg]

Hi all,

I have 8 queries that are all based on one crosstab query. Each of the 8 queries uses a couple different fields from the underlying crosstab query. I also have a union query that joins all eight of the queries into one. Sometimes one or more of the 8 queries fails because the required field is missing from the underlying crosstab query. This causes the entire union query to fail. Is there a way for the union query to ignore the querie(s) that are missing fields and just display the queries that work? Is there a way to have each of the 8 queries display a default when the required field is missing?

Any ideas on getting around this problem are geratly appreciated.

Thanks,
Wendy

 

[Remou]

You could try Nz.

Select Nz(Field1,0) As F1, Nz(Field2,0) As F2, ...

 

[lameid]

You can also specify the column headings properties in the crosstab query to have all possible values so that it will always exist. In the QBE right click the field name and select properties from the context menu. In column headins specify the field list. I do this often with months of the year...

"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

Although I must confess, unioning data off of a crosstab query sounds a lot like selecting from the base table to me. Are you sure you can't use a simple select statement to get the data you want?