When my Consult type option button is set to Auto, I want the filter to omit the SOURCE criteria because the source options (related, unrelated) do not apply to records with an "auto" selection- meaning all records with auto have nothing (null) in their fields for source. So when I add the source part to the criteria ( " AND [SOURCE] & strSOURCE ) and select AUTO, I get no records... :-( ideas? I could go in and right zero or something for autos and then make that part of the source option but I'm guessing there's an easier way just to conditionally apply the filter depending on the first option selection of auto or allo. It is for the allo's that then you can have related or unrelated....
thanks all!
onclick Filterbutton code:
Dim strHCTTYPE As String
Dim strSOURCE As String
Dim strINFUSIONTYPE As String
Dim strCONSULTTYPE As String
Dim strFilter As String
'Build criteria string for HCTTYPE
If IsNull(Me.cboharvestmethod.Value) Then
strHCTTYPE = "Like '*'"
Else
strHCTTYPE = "='" & Me.cboharvestmethod.Value & "'"
End If
'Build critera string for SOURCE
Select Case Me.frasource.Value
Case 1
strSOURCE = "='Related'"
Case 2
strSOURCE = "='Unrelated'"
Case 3
strSOURCE = "Like '*'"
End Select
'Build criteria string for INFUSIONTYPE
If IsNull(Me.cboinfusiontype.Value) Then
strINFUSIONTYPE = "Like '*'"
Else
strINFUSIONTYPE = "='" & Me.cboinfusiontype.Value & "'"
End If
'Build criteria string for CONSULTTYPE
Select Case Me.fratxtype.Value
Case 1
strCONSULTTYPE = "='AUTO'"
Case 2
strCONSULTTYPE = "='ALLO'"
Case 3
strCONSULTTYPE = "Like '*'"
End Select
'Combine criteria later
strFilter = "[HCTTYPE] " & strHCTTYPE & " AND [CONSULTTYPE] " & strCONSULTTYPE & " AND [INFUSIONTYPE] " & strINFUSIONTYPE & " AND [SOURCE] " & strSOURCE
'Apply the filter and swtich it on
With Reports![DataReportA]
.Filter = strFilter
.FilterOn = True
End With