Is Null for Where Criteria

Question

thank you in advance for your help. I can't find a way to get  Is Null  into my where criteria of my SQL. It cannot be a string

"Is Null"  my code:

        myRev = StrConv(Forms!SPIConvertF!Revision.Value, vbUpperCase)          ' Put the revision in caps.

        If myRev = "A" Then                                                                                ' Check to see if the version is A.
            myRevision = "[Is Null]"                                                                       ' A is not listed, it is null.
        Else
            myRevision = [myRev]                                                                          ' If version is not A use what is entered.
        End If
       
    Set db = CurrentDb()                                                                                    ' Set the db to this database.
     
    SQL1 = "SELECT Code1 & PN & Code2 & Desc & Code3 & " _
            & "Qty & Code4 & Code5 & Code6 & Code7 " _
            & "FROM SPIPartsListQ " _
            & "WHERE SPIPartsListDesc.EstNo =" & Forms!SPIConvertF!EstNum _
            & " And SPIPartsListDesc.Revision = """ & [myRevision] & """" _
            & " And SPIPartsListDesc.SegNo = """ & Forms!SPIConvertF!SegNum & """"                           ' Get the first query.

So the problem is that the Revision field in the table was created about a hundred years after the table was built so all of the records prior to the revisions being added are null in this field. So in order to pull these null revision records (which we refer to as Revision A) i need to use Is Null as criteria.  How do i get it in the criteria without making it a string?

Answer 1

Hi Jerry,

How about doing the test right there at the SQL statement assignment? For example:

SQL1 = "SELECT...
    &...
    ...
    & " And SPIPartsListDesc.Revision " & IIf(myRev="A"," Is Null ","=""" & myRev & """" _
    &...

Or, you could break down your WHERE clause into segments. For example:

Dim RevCriteria As String

If myRev = "A" Then
   RevCriteria = " And SPIPartsListDesc.Revision Is Null "
Else
   RevCriteria = " And SPIPartsListDesc.Revision=""" & myRevision & """ "
End If

SQL1 = "SELECT...
    & ...
    ...
    & " WHERE SPIPartsListDesc.EstNo=...
    & " And SPIPartsListDesc.SegNo=...
    & RevCriteria

Hope it helps...

Answer 2

Hi Jerry,

This is how you use the Not operator in combination with Is Null. This way you only retrieve records of which FieldName has a value.

"WHERE Not TableName.FieldName Is Null"

Answer 3

So the problem is that the Revision field in the table was created about a hundred years after the table was built so all of the records prior to the revisions being added are null in this field. So in order to pull these null revision records (which we refer to as Revision A) i need to use Is Null as criteria.  How do i get it in the criteria without making it a string?

Hi Jerry,

Why not making a string?

You can simply use something like:

     & " AND SPIPartsListDesc.Revision IS NULL" _

as part of your sql-string.

Imb.

Answer 4

Thank you all.  DBGuy - this worked great!  Thank you!

            & " And SPIPartsListDesc.Revision " & IIf(myRevision = "A", " Is Null ", "=""" & myRevision & """") _

Thank you all for taking time to help. Very much appreciated.

Jerry 215

Answer 5

Thank you all.  DBGuy - this worked great!  Thank you!

            & " And SPIPartsListDesc.Revision " & IIf(myRevision = "A", " Is Null ", "=""" & myRevision & """") _

Thank you all for taking time to help. Very much appreciated.

Jerry 215

Hi Jerry,

You're welcome! Glad we could assist. Good luck with your project.