Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2006
    Posts
    6

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You are sure this field is the offender? Have you taken it out of the Union and it runs fine?

    Do you still get the error with a UNION ALL?

  3. #3
    Join Date
    Aug 2006
    Posts
    6
    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).

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    make sure that that query which returns the actual value comes ahead of the query with NULL placeholder
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Posts
    6
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it does make sense

    too bad there isn't a CAST function in Access.... is there?

    what about fooling it, like using 937+NULL -- since number+NULL yields NULL, would that work to make it use the right datatype for that column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't know about Access 97 but...

    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).

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    too bad there isn't a CAST function in Access.... is there?
    Kinda. But it being Access it doesn't (of course) bear any relation to ANSI.

    CInt ~ CAST(MyCol AS Integer)
    CBool ~ CAST(MyCol AS Boolean)

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so, jadixon, if the handsome audacious flump cuddler is to be believed, what do you get if you use CInt(NULL)???

    inquiring minds want to know...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    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).
    What a crock - dumbass.

    Code:
    SELECT BooleanTable.BooleanField
    FROM BooleanTable
    UNION ALL
    SELECT NULL
    FROM BooleanTable
    That works great in XP.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    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).
    Oh you handsome genius.

    This
    Code:
    SELECT CBOOL(BooleanField) FROM
    (SELECT BooleanTable.BooleanField
    FROM BooleanTable
    UNION ALL
    SELECT NULL
    FROM BooleanTable) AS UnionOfBooleans
    Doesn't return an error but instead of nulls one gets the dreaded #Error in the resultant query return.

    I wonder if jadixon is attempting to insert the results of this query into a table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Aug 2006
    Posts
    6
    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.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    would you kindly change this --
    Code:
    Select A, B, NULL as C from table1
    Union All A, B, C from table2
    to this --
    Code:
    SELECT A, B, C from table2
    Union All Select A, B, NULL  from table1
    and let me know what happens
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Aug 2006
    Posts
    6
    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......

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You could have a stab at doing this programmatically - at least automating some of the work for you. Want some code or are you ok with it yourself?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •