[AccessD] How to do Like and is null in where clause

I'm not sure exactly what you mean but it sounds like you have a combo event procedure call a sub procedure that updates the where clause of an SQL string.
 
If that is the case, my approach is that I have an existing WHERE clause to the effect that the Primary Key field is not null or the record Deleted Flag = False.  If there is a value in the combo, I append a string to the existing Where clause along the lines:
 
strCondition = " And OfficeID = " & me.cboOfficeID 
else
strCondition = ""

xWhen working with a QueryDef and updating the SQL property, I parse the string and insert the additional condition prior to the ORDER BY clause.  When dynamically setting  something like a JIT subform record source, I generate the entire SQL string on the fly.  Using this approach, the absence of a restriction on the record set returns All.
 
Here is an example used when my standard WHERE clause is a necessary condtion based on a file selected in a single select list box.  The selection of the file will populate a 2nd list box with a number of locations, typically from 1 to about 20 separate locations.  By default all rows in the 2nd list are selected but the user can select or deselect any combination of locations from the 2nd list box.  On click of the cmdFilter button, the RecordSource of a particular subForm on the same parent to the sub form containing the filter lists is set.  In the example, the ElseIf lngJ = lngI results in the condition appended to the WHERE clause being left as "", that being the case when the itemsSelected.count = the listCount so no additional location filter is applied.  Since multiple conditions may apply, I've used IN, =, Not IN and <> depending on if All, none, 1, all but one or more or less than half of the items are selected to get the simplest WHERE clause for JET or the server.  I've left the error handling, setting object to nothing stuff off of the sample below to keep it short.  If no items in the location list are selected, I could disable the cmdFilter button, but it is set to display '' from a tiny lookup table on a negative primary key number so it's fast and doesn't display any 'error' fields should I set the record source to "".  This leaves 0 rows in my continuous sub form in this example.
 
Private Sub cmdFilter_Click()
    Dim lst As ListBox
    Dim strSql As String
    Dim strWhere As String
    Dim lngI As Long
    Dim lngJ As Long
    Dim lngC As Long
    Set lst = Me.lstLocations
    lngI = lst.ItemsSelected.Count
    lngJ = lst.ListCount
    If lngI > 0 Then
        'we have at least one item to process
        If lngI = 1 Then
            'restrict to one location '= item
            For lngC = 0 To lngJ - 1
                If lst.Selected(lngC) = True Then
                    strWhere = " = '" & lst.Column(0, lngC) & "'"
                    Exit For
                End If
            Next
        ElseIf lngJ = lngI Then
            'All locations are selected, don't apply a location filter
        ElseIf lngJ - lngI = 1 Then
            'only one is not selected, use '<> item
            For lngC = 0 To lngJ - 1
                If lst.Selected(lngC) = False Then
                    strWhere = " <> '" & lst.Column(0, lngC) & "'"
                    Exit For
                End If
            Next
        Else
            'more than 1 selected but not all
            If lngI * 2 > lngJ Then
                'more than 1/2 of the locations are selected - 'not in(   ) list
                For lngC = 0 To lngJ - 1
                    If lst.Selected(lngC) = False Then
                        strWhere = strWhere & ", '" & lst.Column(0, lngC) & "'"
                    End If
                Next
                strWhere = " Not In(" & Mid$(strWhere, 3) & ")"
            Else
                'fewer than 1/2 of the possible locations are selected - 'in(  ) list
                For lngC = 0 To lngJ - 1
                    If lst.Selected(lngC) = True Then
                        strWhere = strWhere & ", '" & lst.Column(0, lngC) & "'"
                    End If
                Next
                strWhere = " In(" & Mid$(strWhere, 3) & ")"
            End If
        End If
        If Len(strWhere) Then
            strWhere = " And Location" & strWhere
        End If
        'base WHERE clause
        strWhere = " Where SageFileID =" & Me.lstFileID & strWhere
        strSql = "SELECT tblCosts.GTMCostCode AS Code, tblCosts.GTMDescrip AS Description, Sum(" & _
          "tblCosts.Quantity) AS Quantity, tblCosts.GTMUnit AS Unit, Sum(tblCosts.MaterialCost) " & _
          "AS Material, Sum(tblCosts.LabourHours) AS Hours, Sum(tblCosts.LabourCost) AS Labour, " & _
          "Sum(tblCosts.EquipCost) AS Equipment, Sum(tblCosts.SubContrCost) AS SubContr FROM tblCosts"
        strSql = strSql & strWhere & " GROUP BY tblCosts.GTMCostCode, tblCosts.GTMDescrip, tblCosts.GTMUnit"
    Else
        'Bound form recordset with no records and quick return from server on tiny 4 record lookup table
        strSql = "SELECT '' AS Code, '' AS Description, '' AS Quantity, '' AS Unit, '' " & _
          "AS Material, '' AS Hours, '' AS Labour, '' AS Equipment, '' AS SubContr FROM tblBidType " & _
          "Where BidTypeID = -1"
        'nothing selected, don't pass in a field or criteria
    End If
    Forms.frmEstimate.sfrmJobCost.Form.RecordSource = strSql
End Sub

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com


 
> Date: Thu, 28 Oct 2010 15:58:13 -0400
> From: jwcolby at colbyconsulting.com
> 
> I have a query where I need to select sets of data. I am looking at existing data in a field, 
> status codes, which I display in a combo so that the user can select sets of records based on those 
> codes. I basically pass the combo box value into the where clause (through a fltr() method) and 
> back come the filtered results. In order to handle the "*" (all) I use a like Fltr().
> 
> The problem is that if the value in the status is an empty string, the combo returns a null. Null 
> and Like are mutually exclusive.
> 
> I finally got around this by testing the combo in the after update of the combo and if the combo 
> value is null, passing a "" to fltr() and thus into the where of the query.
> 
> I'm just wondering if anyone has found another way to do this.
> 
> -- 
> John W. Colby
> www.ColbyConsulting.com