conditional strFilter formatting/code

[lbaccess101]

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

[pbaldy]

I would just leave it out. Null won't get returned with Like, so you can add "OR Source Is Null" but then you're mixing AND with OR so you'd have to add parentheses. I don't really like the way you're separating the field from the filter, but perhaps that's just me.

[June7]

Please post code between CODE tags to preserve indentation and readability.

Try:

strFilter = "[HCTTYPE] " & strHCTTYPE & " AND [CONSULTTYPE] " & strCONSULTTYPE & " AND [INFUSIONTYPE] " & strINFUSIONTYPE & IIf(strCONSULTTYPE Like "*Auto*", "", " AND [SOURCE] " & strSOURCE)

[lbaccess101]

Please post code between CODE tags to preserve indentation and readability.

Try:

strFilter = "[HCTTYPE] " & strHCTTYPE & " AND [CONSULTTYPE] " & strCONSULTTYPE & " AND [INFUSIONTYPE] " & strINFUSIONTYPE & IIf(strCONSULTTYPE Like "*Auto*", "", " AND [SOURCE] " & strSOURCE)

That was brilliant!!! I was about to try and Iff there but would have had no idea how to do it... is the syntax guide for this kind of thing somewhere out there?
Thank you June7!! bless your soul!

[June7]

Guide for what - the syntax of IIf() function - yes, it's out there. But where to use IIf() is up to developer creative thinking and applying logic.

[lbaccess101]

Great... I guess I mean, those commas and place markers and quotes... :/ it's okay if not. oh well, thanks! IIf(strCONSULTTYPE Like "*Auto*", "", " AND [SOURCE] " & strSOURCE)
I'm clearly not versed enough for pure logic just yet. thx again

[June7]

Well, reference to VBA variables would not be within quote marks. Literal text goes between quote or apostrophe marks. The commas are separating function arguments. Those rules are universal, not just applied to IIf() function.

The first argument of the IIf() is a condition to be tested. If the condition is true then return the result of the IfTrue argument, if the condition is false then return the result of the IfFalse argument.

In this case, the IIf() is testing the value of variable strCONSULTTYPE.

And the IfFalse argument expression is just your original concatenation.

Keep in mind that both the IfTrue and IfFalse arguments must have expressions that produce valid results, otherwise the entire IIf() will error. For instance, if one of the arguments is an expression that divides by some field value and that field contains 0, the expression will error because division by 0 is not possible.