none
Is Null expression in query RRS feed

  • Question

  • I need to retrieve all records that has a specific criteria and the "completed" property of them is  False or Null. When I do it in query design it works properly but when I want to imply it in VBA, the result is not what I want. The SQL expression in query design is:

    SELECT tblInsurancePolicy.InsurancePolicyID, tblInsurancePolicy.InsuranceType, tblInsurancePolicy.InsurancePolicyNumber, tblAccount.FinalName, tblInsurancePolicy.InsurancePolicyIssueDate, tblInsurancePolicy.StartDate, tblInsurancePolicy.EndDate, tblInsurancePolicy.CarModel, tblExtendingPolicyForm.RemindDate, tblExtendingPolicyForm.Priority, tblExtendingPolicyForm.Completed, tblInsurancePolicy.Lpp1, tblInsurancePolicy.Lpp2, tblInsurancePolicy.Lpp3, tblInsurancePolicy.Lpp4, tblInsurancePolicy.MotorCycle
    FROM (tblAccount INNER JOIN tblInsurancePolicy ON tblAccount.AccountID = tblInsurancePolicy.AccountID) LEFT JOIN tblExtendingPolicyForm ON tblInsurancePolicy.InsurancePolicyID = tblExtendingPolicyForm.InsurancePolicyID
    WHERE (((tblInsurancePolicy.InsuranceType)=23) AND ((tblInsurancePolicy.EndDate) Between 970301 And 970331) AND ((tblExtendingPolicyForm.Completed)=False Or (tblExtendingPolicyForm.Completed) Is Null))
    ORDER BY tblInsurancePolicy.EndDate;
    

    and I tried to do it in VBA but I have some problems with the expression "Is Null". The VBA code of the above sql is:

    Private Sub cmdOk_Click()
        Dim strWhere As String
        Dim strInsuranceType As String
        Dim blnZeroHide As Boolean
    
        
        blnZeroHide = Me.chkZeroHide
        strInsuranceType = Me.cboInsuranceType.Column(1)
        strWhere = "True"
        If Not IsNull(Me.txtStartDate) Then
            strWhere = strWhere & " AND qryExtendingList.EndDate >= " & _
            Me.txtStartDate
        End If
        
        If Not IsNull(Me.txtEndDate) Then
            strWhere = strWhere & " AND qryExtendingList.EndDate <= " & _
            Me.txtEndDate
        End If
        
        If Not IsNull(Me.cboInsuranceType) Then
            strWhere = strWhere & " AND qryExtendingList.InsuranceType= " & _
            Me.cboInsuranceType
        End If
        
        If blnZeroHide = True Then
            strWhere = strWhere & " AND qryExtendingList.Completed = False Or qryExtendingList.Completed Is Null"
        End If
        
        DoCmd.OpenForm FormName:="frmInsurancePolicyExtending", View:=acFormDS, WhereCondition:=strWhere, OpenArgs:=strAccountName
        Me.Visible = False
        
       
    End Sub

    I want that the query shows me all the records that are in a specified date and have a specified type and their "completed" property of them is false or are null , but when I run the above code it show all fields that their "completed" properties are null and ignore the period and type that I specified in query criteria.!



    Karim Vaziri Regards,

    Monday, June 11, 2018 9:26 PM

Answers

  • You have one Boolean OR operation, so you need to force that to evaluate independently of the AND operations with:

     strWhere = strWhere & " AND (qryExtendingList.Completed = FALSE OR qryExtendingList.Completed IS NULL)"

    However, if Completed is a column of Boolean (Yes/No) data type then it cannot be NULL.  A Boolean column can only have values of TRUE or FALSE.  If it is necessary to implement 3-way logic, then a column of Integer Number data type should be used, and NULL allowed (Required property = False).  The column should allow values of 0 and -1 only, which can be controlled by the ValidationRule property, which should allow only NULL or 0 or -1.

    Ken Sheridan, Stafford, England

    • Proposed as answer by Terry Xu - MSFT Tuesday, June 12, 2018 6:23 AM
    • Marked as answer by kvaziri Tuesday, June 12, 2018 11:53 AM
    Monday, June 11, 2018 10:52 PM

All replies

  • Hi Karim,

    As you indicate, the SQL in the QueryDef gives a correct result. In your code you do not use parentheses and that will undoubtedly not lead to the desired result, especially in this case with AND and OR operators. Make your code in VBA such that it gives the same result as in the QueryDef.

    Monday, June 11, 2018 10:04 PM
  • You have one Boolean OR operation, so you need to force that to evaluate independently of the AND operations with:

     strWhere = strWhere & " AND (qryExtendingList.Completed = FALSE OR qryExtendingList.Completed IS NULL)"

    However, if Completed is a column of Boolean (Yes/No) data type then it cannot be NULL.  A Boolean column can only have values of TRUE or FALSE.  If it is necessary to implement 3-way logic, then a column of Integer Number data type should be used, and NULL allowed (Required property = False).  The column should allow values of 0 and -1 only, which can be controlled by the ValidationRule property, which should allow only NULL or 0 or -1.

    Ken Sheridan, Stafford, England

    • Proposed as answer by Terry Xu - MSFT Tuesday, June 12, 2018 6:23 AM
    • Marked as answer by kvaziri Tuesday, June 12, 2018 11:53 AM
    Monday, June 11, 2018 10:52 PM
  • Dear Sheridan,

    Thanks for your help. The parentheses worked and the result is OK.

    The COMPLETED field is a Boolean and has null value. I think it may have a reason. My back end database is SQL and I think it is for this reason! Maybe.


    Karim Vaziri Regards,

    Tuesday, June 12, 2018 11:57 AM