Minor Change to Filter

[speakers_86]

I found this code online, and I made a few mods to it. Down at 5th combo, when me.chkShow is -1, how can I include null values there?

Private Function StockSearch()

    'Thanks for this!
    'http://www.access-programmers.co.uk/forums/showthread.php?t=157601


    'Error Trapping
    Dim ErrorName As String
    ErrorName = "StockSearch_Error"                   'Add a name for this error
    Dim ExitThisSub As String
    ExitThisSub = "Exit_" & ErrorName
    ProcedureError = "VBA Document-Form_frmDatabaseWindow-StockSearch-1"    'Something descriptive
    If CurrentProject.AllForms("frmsettings").IsLoaded Then
        If [Forms]![frmsettings].chkEnableErrorTrapping = -1 Then
            On Error GoTo ErrorName
        End If
    Else
        On Error GoTo ErrorName
    End If
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '   On Error GoTo Error_StockSearch
    Dim FilterClause As String, D As Long
    '   'Hold whether we Use AND or OR in our Filter Criteria
    D = 1                                             'Me.DirectionGrp.Value

    '1st Combo - Wood Grade - Assuming the Table Field 'Grade' is of a Text DataType
    If Nz(Me.cboTypeTXT.Column(0), 0) > 0 Then
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[typetxt]='" & Me.cboTypeTXT.Value & "'"
    End If
    'Error Handling
    ProcedureError = "VBA Document-Form_frmDatabaseWindow-StockSearch-2"
    '2nd Combo - Wood Treatment - Assuming the Table Field 'Treatment' is of a Text DataType
    If Nz(Me.cboGroup1.Column(0), 0) > 0 Then
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[group1]='" & Me.cboGroup1.Value & "'"
    End If
    'Error Handling
    ProcedureError = "VBA Document-Form_frmDatabaseWindow-StockSearch-3"

    '3rd Combo - Stock Location - Assuming the Table Field 'Location' is of a Text DataType
    If Nz(Me.cboGroup2.Column(0), 0) > 0 Then
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[group2]='" & Me.cboGroup2.Value & "'"
    End If
    'Error Handling
    ProcedureError = "VBA Document-Form_frmDatabaseWindow-StockSearch-4"

    '4th Combo - Wood Drying (kilned) - Assuming the Table Field 'Drying' is of a Text DataType
    If Len(Me.cboGroup3.Value & "") > 0 Then
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[group3]='" & Me.cboGroup3.Value & "'"
    End If
    'Error Handling
    ProcedureError = "VBA Document-Form_frmDatabaseWindow-StockSearch-5"

    '5th Combo - Wood Finish (S4S or whatever) - Assuming the Table Field 'Finish' is of a Text DataType
    If Me.chkShow = -1 Then
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[show]=-1"
    Else
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[show]=0"
    End If

    '   '5th Combo - Wood Finish (S4S or whatever) - Assuming the Table Field 'Finish' is of a Text DataType
    '   If Len(Me.chkShow.Value & "") > 0 Then
    '      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
    '      FilterClause = FilterClause & "[Finish]='" & Me.Finish.Value & "'"
    '   End If
    '
    '   '6th Combo - Nominal Width - Assuming the Table Field 'Nominal Width' is of a Text DataType
    '   If Len(Me.[NominalWidth].Value & "") > 0 Then
    '      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
    '      FilterClause = FilterClause & "[Width Nom]=" & Me.[NominalWidth].Value
    '   End If
    '
    '   '7th Combo - Nominal Thickness - Assuming the Table Field 'Nominal Thickness' is of a Text DataType
    '   If Len(Me.[NominalThickness].Value & "") > 0 Then
    '      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
    '      FilterClause = FilterClause & "[Thick Nom]=" & Me.[NominalThickness].Value
    '   End If
    '
    '   '8th Combo - Actual Width - Assuming the Table Field 'Actual Width' is of a Text DataType
    '   If Len(Me.[ActualWidth].Value & "") > 0 Then
    '      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
    '      FilterClause = FilterClause & "[Actual Width]=" & Me.[ActualWidth].Value
    '   End If
    '
    '   '9th Combo - Actual Thickness - Assuming the Table Field 'Actual Thickness' is of a Text DataType
    '   If Len(Me.[ActualThickness].Value & "") > 0 Then
    '      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
    '      FilterClause = FilterClause & "[Actual Thick]=" & Me.[ActualThickness].Value
    '   End If
    'Error Handling
    ProcedureError = "VBA Document-Form_frmDatabaseWindow-StockSearch-6"
    'Fill this Form wide variable so that it can be used for
    'the Report.
    CurrentFilter = FilterClause: FilterClause = ""

    'Place our created Filter Criteria into the Filter property of SubForm.
    Forms("frmdatabasewindow")("frmdatabasewindowsub").Form.Filter = CurrentFilter
    'Turn on the Filter
    Forms("frmdatabasewindow")("frmdatabasewindowsub").Form.FilterOn = True
    '
    'Exit_StockSearch:
    '   Exit Function
    '
    'Error_StockSearch:
    '   MsgBox "StockSearch Function Error" & vbCr & vbCr & _
        '          Err.Number & " - " & Err.Description, vbExclamation, _
        '          "Stock Search Error"
    '   Resume Exit_StockSearch


    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ExitThisSub:
    Exit Function
ErrorName:
    Call LogError(Err.Number, Err.Description, ProcedureError)
    Resume ExitThisSub
End Function

 

[llkhoutx]

Try:

If Me.chkShow = -1 or Me.chkSnow & "" = "" Then

 

[vbaInet]

The same way you include Null values in a query. OR Is Null

 

[speakers_86]

I thought I tried that vbainet. I'll give a try again later.

 

[vbaInet]

Ok, the correct syntax is OR [FieldName] Is Null

 

[speakers_86]

Thanks for your answer, vbaInet, but that is a type mismatch. Note that show is a checkbox control. The field in the table is an integer number. I've tried all sorts of variants, none of them have worked. You can see remnants of one of my attempts commented out at the end of the line there.

    '5th Combo - Wood Finish (S4S or whatever) - Assuming the Table Field 'Finish' is of a Text DataType
    If Me.chkShow = -1 Then
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[show]=-1" Or [Show] Is Null '& " AND " & "[show]=" & IsNull
    Else
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[show]=0"
    End If

 

[vbaInet]

speakers, watch your spacing and the way you are concatenating. Look at the code line and again and you will see where you went wrong.

NB: Is Null is not the same as IsNull. And Is Null shouldn't be inside the quotes and read as a string.

 

[speakers_86]

I put exactly what you said. The part you are referring to was commented out. Here it is again without that comment. Edit- the error is a type mismatch.

    '5th Combo - Wood Finish (S4S or whatever) - Assuming the Table Field 'Finish' is of a Text DataType
    If Me.chkShow = -1 Then
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[show]=-1" Or [Show] Is Null

 

[vbaInet]

You're still doing the same thing:

FilterClause = FilterClause & [COLOR=Red]"[/COLOR][show]=-1 Or [Show] Is Null [COLOR=Red]"[/COLOR]

 

[speakers_86]

I had to curse at myself for that.

 

[vbaInet]

All good now?

 

[speakers_86]

Oh yeah, thanks. I new the moment I saw what you did it would work. I'm no good with SQL, I always rely on the Access graphical layout (not the wizard). Now that I think about it, I probably could have made a mock query there to figure this out.

 

[vbaInet]

You could have