Unanswered: Null in union query doesn't work in Access 2000
I have a database in Access 97 that uses a NULL to hold a place in a union query. The "matching" field in the second query is a yes/no field. When I converted the database into Access 2000, I now get the following error when I run this query: "Data type mismatch in criteria expression". Any ideas on how to correct this problem?
First, the union is a union all. Second, eliminating field by field, I have found that when any numeric or yes/no field is unioned with the null it appears to have problems.
The query with the problem is actually a union all query of another union all query and a "regular" query. The 2 queries each work fine, and the one union all query seems to be ok, unioning the null with a numeric field. But when I try to union all in the resultant query, I get an error.
These queries all worked fine in 97, it is just in 2000 I am having issues. I am hoping to find a simple solution so I don't have to rewrite the application (there are many queries like this in the app).
Moving the query did not work. The queries are set up to where one returns data and the other returns null, but in opposite fields (if that makes sense). So moving the one query before the other does not solve the problem.
All columns in a UNION query must be UNION compatible. In A2k + (and maybe not A97 - I don't know) Boolean fields are dual state (-1, 0) - there can be no null. Bits in SQL Server are tristate (0, 1, NULL) so maybe A97 used to be like this???? Or maybe A97 was just more laid back about enforcing union compatiibility.
Anyway - I'd put my money on that - you can't have a null Boolean. Would a false be a big problem? Otherwise you will need to convert to some numeric field (Integer would allow NULL, 0 and -1).
I get #Name in all fields when I get the error message (this does not go into a table). Without listing the entire query structure that I have (I "inherited" this crappy database), here is what I have:
Select A, B, NULL as C from table1
Union All A, B, C from table2
where C is a boolean. This was no problem in 97. I have been playing with changing the NULL to 0, but there are about 10 fields in the query that this happens with and I have not changed them all just yet (other projects have had priority).
I also have access 97 AND 2000 loaded on the same machine, so my next test will be to put the database on a machine that only has access 2000 and see what it does.
There are so many queries (poorly designed and written), that I really dread going through each one to change the nulls to something else. But if I don't find another solution, I guess that is what I may have to do.
When I put NULL into the union query, the query itself works ok, but then my form that is dependent on the query does not have the correct column title and then prompts for the data.
I tried running on the system with just access 2000 and that did not work.
I did go into my queries (these are actually queries of queries) and changed the NULL to 0. This did work. I got zeros in some fields that may not be the desired result (as opposed to a blank field), but at least it ran.
I guess I will need to go through and find every query to remove the NULL, but I really don't think I should have to. I would imagine there is something that needs to be included/converted or something that would make this work without changing 300 queries. Leave it to Microsoft......