Results 1 to 3 of 3

Thread: Null In Select

  1. #1
    Join Date
    Dec 2011
    Posts
    35

    Unanswered: Null In Select

    So the issue is I have two tables and the field SERVICE TYPE in both tables can contain null, so when I try matching it I get nothing back, so how can fix this ??? I know I can use the Nz on my first select however this did not work Nz(rst![SERVICE TYPE], Null)


    Set rst = CurrentDb.OpenRecordset("SELECT * FROM AppendZips;")
    Set rst2 = CurrentDb.OpenRecordset("SELECT DISTINCT * FROM Master_Database WHERE [HG ID] = " & HGID & " AND [SERVICE TYPE] = '" & rst![SERVICE TYPE] & "' ;")

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You cannot perform an INNER JOIN operation which test for an equality between columns that can contain Null values, because the expression:
    Code:
    (Null = Null)
    yields Null, not True (you can try it in the Immediate window of the VBA editor).

    What you could try to do would be to use:
    Code:
    ON Nz([ColumnA], "") = Nz([ColumnB], "")
    However this would produce extra rows.

    You could also add a WHERE clause to eliminate the rows containing Null values:
    Code:
    WHERE [ColumnA] Is Not Null AND [ColumnB] Is Not Null
    In any case, the result set cannot be accurate.
    Have a nice day!

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Nz(rst![SERVICE TYPE], Null)
    1) You are using the Nz() function incorrectly. Using the function already presumes that the value may be Null or Zero (hence the name, Nz). The value after the comma is meant to "un-zero" and "un-null" the null/zero value, and therefore must contain a valid value. In the case of a Null string value, you would normally use " "; in the case of a Null numeric value, you would use 0.

    Therefor, change the formula to
    Code:
    Nz(rst![SERVICE TYPE], " ")
    or
    Code:
    Nz(rst![SERVICE TYPE], 0)
    depending on the data type of SERVICE TYPE.

    2) If you are joining your two tables on the SERVICE TYPE field, you will have problems, because the JOIN takes place BEFORE the Nz() function does its work. In order to join on the SERVICE TYPE field, you first have to run preliminary queries on each table separately, using the Nz() function to change the null value to a proper value, and then using the query joining the two.

    Sam

Posting Permissions

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