Union Query - Data Type Mismatch

[The Brown Growler]

Hi,

I am using a union query to merge the results of two separate queries. For one of these separate queries I am setting the entire contents of a date field to null, similarly for a number field. However, when I run the actual union query I am getting the data type mismatch error.

I presume it is caused by the date and number field in one query not being able to be merged with the same date and number field from the other query when unified ?

I can get the union query to run if I set the date and number field formats to text in the query I require the blank cells by using "". However, I then lose the date and number format of these fields in the union query.

Any help most appreciated.

Rgds
Growlos

 

[Brianwarnock]

Since, as you have found, Null is not a valid value for Date or Numeric data types I think that you may have to have a rethink on what you are attempting.

Brian

 

[SOS]

Is this on linked SQL Server tables or on Access tables? Also, is it for creating a single field or two fields? I don't have a problem in doing this, but need more information as to what you are doing to make a better assessment.

Brian - NULL is a valid value for a date field. In fact a date field can ONLY be NULL or have a date. Number fields can have NULL as well. So, I'm not seeing what the problem is at the moment.

 

[The Brown Growler]

SOS,

It is in a standalone version of access on a local PC using 2 queries based on tables.

I have query A which contains for example 12 fields, one of which is a date field and one of which is a number field. All these fields are correctly populated with data when I run the query.

I have query B which contains the same 12 fields as query A but for the date field and the number field in this query I wish to have blank values and I have used the following to get the nulls using the query grid

DateField: Null
NumberField: Null

Now, when I try to unify the output of query A and query B in a union query I get the Data Type Mismatch error and it is caused by the nulls from query B. If I allow query B to have actual date and number values then the union runs OK.


Regards
Growlos

 

[SOS]

Can you upload a copy of the database so we can dig into it to see?