Select Case trouble

[batwings]

Hello

Can someone please tell me why my Select Case statement isn't working? or point me in a better way to achieve my goal.

I previously posted this wrongly in the forms forum but I think it belongs here and I apologise.

The [ClearanceDate] Field contains Dates in the form of a Text String example, 23-03-2008

Using the VBA below I am trying to use the Case Select to return all records that have a date, all records with no date and all records regardless of whether there is a date or not.

I have an option group and three redio buttons, Op, Closed and All the Open = No date works fine, the Closed = there is a date works fine but the All = seems to give me the same as the Closed button.

Oh before the Select Case there are 3 combo boxes that progressively filter the recordset System>Partsystem>Subsystem, I just need to further filter by Open,Closed or All

'Create SQL String and Option Group Select Case
Private Sub cmdApplyFilter_Click()
    Dim strSystem As String
    Dim strPartsystem As String
    Dim strSubsystem As String
    Dim strClearanceDate As String
    Dim strFilter As String
' Check that the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "rptDetailFilter") <> acObjStateOpen Then
        MsgBox "Open the report first Ya dummy."
        Exit Sub
    End If
' Build criteria string for System field
    If IsNull(Me.cboSystem.Value) Then
        strSystem = "Like '*'"
    Else
        strSystem = "='" & Me.cboSystem.Value & "'"
    End If
' Build criteria string for PartSubsystem field
    If IsNull(Me.cboPartsystem.Value) Then
        strPartsystem = "Like '*'"
    Else
        strPartsystem = "='" & Me.cboPartsystem.Value & "'"
    End If
' Build criteria string for Subsystem field
    If IsNull(Me.cboSubsystem) Then
        strSubsystem = "Like '*'"
    Else
        strSubsystem = "='" & Me.cboSubsystem.Value & "'"
    End If
' Build criteria string for ClearanceDate field
    Select Case Me.fraClearanceDate.Value
        Case 1
            strClearanceDate = "Is Null"
        Case 2
            strClearanceDate = "Is Not Null"
        Case 3
            strClearanceDate = "Like '*'"
    End Select
 
' Combine criteria strings into a WHERE clause for the filter
    strFilter = "[System] " & strSystem & " AND [Partsystem] " & strPartsystem & " AND [Subsystem] " & strSubsystem & " AND [ClearanceDate] " & strClearanceDate
' Apply the filter and switch it on
    With Reports![rptDetailFilter]
        .Filter = strFilter
        .FilterOn = True
        Reports![rptDetailFilter].txtReportTitle.Value = _
        "System: " & Me.cboSystem.Value _
        & vbCrLf & "PartSystem: " & Me.cboPartsystem.Value _
        & vbCrLf & "Subsystem: " & Me.cboSubsystem.Value
        '& vbCrLf & "SubSys_Description: " & Me.
    End With
End Sub

 

[HiTechCoach]

If you want "all" , I would urge you to remove any criteria for that field since it is a not needed.

Example:

' Build criteria string for ClearanceDate field
    Select Case Me.fraClearanceDate.Value
        Case 1
            strClearanceDate = "  AND [ClearanceDate] Is Null"
        Case 2
            strClearanceDate = " AND [ClearanceDate] Is Not Null"
        Case 3
            strClearanceDate = ""
    End Select
 
' Combine criteria strings into a WHERE clause for the filter
    strFilter = "[System] " & strSystem & " AND [Partsystem] " & strPartsystem & " AND [Subsystem] " & strSubsystem & strClearanceDate

If your really want to put in the criteria then use:

' Build criteria string for ClearanceDate field
    Select Case Me.fraClearanceDate.Value
        Case 1
            strClearanceDate = "  AND [ClearanceDate] Is Null"
        Case 2
            strClearanceDate = " AND [ClearanceDate] Is Not Null"
        Case 3
            strClearanceDate = " AND ( [ClearanceDate] Like '*' or [ClearanceDate] Is Null ) "
    End Select
 
' Combine criteria strings into a WHERE clause for the filter
    strFilter = "[System] " & strSystem & " AND [Partsystem] " & strPartsystem & " AND [Subsystem] " & strSubsystem & strClearanceDate

or maybe

' Build criteria string for ClearanceDate field
    Select Case Me.fraClearanceDate.Value
        Case 1
            strClearanceDate = "  AND [ClearanceDate] Is Null"
        Case 2
            strClearanceDate = " AND [ClearanceDate] Is Not Null"
        Case 3
            strClearanceDate = " AND  Nz([ClearanceDate], "") Like '*' "
    End Select
 
' Combine criteria strings into a WHERE clause for the filter
    strFilter = "[System] " & strSystem & " AND [Partsystem] " & strPartsystem & " AND [Subsystem] " & strSubsystem & strClearanceDate

 

[batwings]

I meant to say that the attached view of my form is used for filtering a report! The 1st and 2nd options you suggessted return no record at all for "Open" and only the closed records for "Closed" and "All" The 3rd option crashes with a syntax error on "All"

 

[HiTechCoach]

It would help to see the new VBA code. Would you please post the new VBA code you are using?