none
Is Null for Where Criteria RRS feed

  • 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?

    Thursday, August 2, 2018 8:46 PM

Answers

  • 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

    • Marked as answer by Jerry215 Thursday, August 2, 2018 11:07 PM
    Thursday, August 2, 2018 11:07 PM

All replies

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

    Thursday, August 2, 2018 9:01 PM
  • 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"

    Thursday, August 2, 2018 9:07 PM
  • 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.

    Thursday, August 2, 2018 9:08 PM
  • 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

    • Marked as answer by Jerry215 Thursday, August 2, 2018 11:07 PM
    Thursday, August 2, 2018 11:07 PM
  • 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.

    Friday, August 3, 2018 3:01 PM