multiselect list box code for filter form

bymarce · Sep 11, 2009

I have a filter form (based on Allen Browne's code). This portion of the
code is supposed to build a string based on a multiselect list box but it's
returning nothing no matter how many items are selected. Why isn't this
working? Thanks.
Marcie
'Loop through the ItemsSelected in the list box.
Dim strProperty As String, PropertyLen As Long, varItem As Variant,
strQ As String
strQ = """"
With Me.fProperty
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strProperty = strProperty & strQ & .Column(0, varItem) &
strQ & ", "
End If
Next
End With
Debug.Print strProperty

PropertyLen = Len(strProperty) - 2
Debug.Print PropertyLen

If PropertyLen > 0 Then
strProperty = "([Property] IN (" & Left$(strProperty,
PropertyLen) & "))"
strWhere = strWhere & strProperty & " AND "
End If

Douglas J. Steele · Sep 11, 2009

The code you've presented is syntactically correct (which is to be expected
from Allen's work).

How are you using that code? How are you determining that nothing's being
returned?

bymarce · Sep 17, 2009

Here's the entire code for this button. I have about 20 unbound controls
(combo, list, and text boxes) in the header of the form for filtering
experimental data based on 3 tables: MLOBOOK (sample catalog), DATA, and
Conditions. I based this on Allen's filter form example and filtering based
on a field in a subform. When I select only values in fProperty my form pops
up a message that says "No Criteria". Also debug.print PropertyLen returns -2
in the immediate window. I'm also trying to have this button add to the
filter string using "OR" if the form is already filtered rather than starting
from scratch every time. Thanks for your help.
Marcie

Private Sub cmdFilterAnd_Click() 'Adapted from
http://allenbrowne.com/ser-62code.html

Dim segCND As Long
Dim segNum As Long
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
segNum = 0
Dim strCND As String
Dim lenCND As Long
If Me.FilterOn = True Then
strWhere = Me.Filter
strWhere = "(" & strWhere & ") OR ("
segNum = 1
'Debug.Print strWhere
End If

strCND = Me.RecordSource
lenCND = Len(strCND)
'Debug.Print lenCND
If lenCND > 654 Then
strCND = Right(strCND, (lenCND - 654))
strCND = Left(strCND, Len(strCND) - 3)
'Debug.Print strCND
strCND = "(" & strCND & ") OR ("
segCND = 1
Else
strCND = ""
segCND = 0
End If

Dim strSQL As String
If IsNull(Me.fcboCnd1) And segCND = 0 Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "SELECT MLOBOOK.Description, Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod, Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM MLOBOOK INNER JOIN Data ON (MLOBOOK.CatalogID =
Data.CatalogID) AND (MLOBOOK.CatalogYear = Data.CatalogYear) " & _
"AND (MLOBOOK.CatalogPrefix = Data.CatalogPrefix) WHERE
(((Data.CatalogYear) Is Not Null));"
Else
If Not IsNull(Me.fcboCnd1) Then
strCND = strCND & "((Conditions.ConditionName) Like """ &
Me.fcboCnd1 & """) And "
If Not IsNull(Me.fCnd1L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd1L
& ") And "
End If
If Not IsNull(Me.fCnd1H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd1H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits1) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits1 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd2) Then
strCND = "((Conditions.ConditionName) Like """ & Me.fcboCnd2
& """) And "
If Not IsNull(Me.fCnd2L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd2L
& ") And "
End If
If Not IsNull(Me.fCnd2H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd2H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits2) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits2 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd3) Then
strCND = "((Conditions.ConditionName) Like """ & Me.fcboCnd3
& """) And "
If Not IsNull(Me.fCnd3L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd3L
& ") And "
End If
If Not IsNull(Me.fCnd3H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd3H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits3) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits3 & """) And "
End If
End If

lenCND = Len(strCND) - 5
strCND = Left(strCND, lenCND)
'Debug.Print strCND
If segCND = 1 Then
strCND = strCND & ")"
End If
strSQL = "SELECT MLOBOOK.Description, Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod, Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM Conditions INNER JOIN ((MLOBOOK INNER JOIN Data ON
(MLOBOOK.CatalogID = Data.CatalogID) AND (MLOBOOK.CatalogYear " & _
"= Data.CatalogYear) AND (MLOBOOK.CatalogPrefix =
Data.CatalogPrefix)) INNER JOIN ConditionsDataJunction " & _
"ON Data.ID = ConditionsDataJunction.DataID) ON Conditions.ID =
ConditionsDataJunction.ConditionsID " & _
"WHERE (((Data.CatalogYear) Is Not Null) AND (" & strCND & "));"
'Debug.Print strSQL
Me.RecordSource = strSQL
End If

Dim strPrefix As String
Dim strYear As String
Dim strID As String

If Not IsNull(Me.cboMLOL) And IsNull(Me.cboMLOH) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix & """)
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix & """)
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And Not IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix & """)
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) and "
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix & """)
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) and "
End If
'Debug.Print strWhere

'Loop through the ItemsSelected in the list box.
Dim strProperty As String, PropertyLen As Long, varItem As Variant,
strQ As String
strQ = """"
With Me.fProperty
For Each varItem In .ItemsSelected
'Build up the filter from the bound column (hidden).
strProperty = strProperty & strQ & .Column(varItem) &
strQ & ", "
Next varItem
End With
Debug.Print strProperty

PropertyLen = Len(strProperty) - 2
Debug.Print PropertyLen

If PropertyLen > 0 Then
strProperty = "([Property] IN (" & Left$(strProperty,
PropertyLen) & "))"
strWhere = strWhere & strProperty & " AND "
End If
'Remove trailing comma. Add field name, IN operator, and
brackets.

'Loop through the ItemsSelected in the list box.
Dim strMethod As String, methodlen As Long
With Me.fMethod
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strMethod = strMethod & strQ & .ItemData(varItem) & strQ & ","
End If
Next
End With

methodlen = Len(strMethod) - 1
If methodlen > 0 Then
strMethod = "[TestMethod] IN (" & Left$(strMethod, methodlen) &
")"
End If
If Len(strWhere) > 0 And methodlen > 0 Then
strWhere = strWhere & "(" & strMethod & ") AND "
ElseIf Len(strWhere) <= 0 And methodlen > 0 Then
strWhere = strMethod & " AND "
Else
strWhere = strWhere
End If

'Remove trailing comma. Add field name, IN operator, and brackets.
'Deselect and clear filter controls.

If Not IsNull(Me.fResultL) Then
strWhere = strWhere & "([RNumeric] >= " & Me.fResultL & ") AND "
End If

If Not IsNull(Me.fResultH) Then
strWhere = strWhere & "([RNumeric] <= " & Me.fResultH & ") AND "
End If

'Loop through the ItemsSelected in the list box.
Dim strText As String, TextLen As Long
With Me.fResultText
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strText = strText & strQ & .ItemData(varItem) & strQ & ","
End If
Next
End With

TextLen = Len(strText) - 1
If TextLen > 0 Then
strText = "[RText] IN (" & Left$(strText, TextLen) & ")"
End If
If Len(strWhere) > 0 And TextLen > 0 Then
strWhere = strWhere & "(" & strText & ") AND "
ElseIf Len(strWhere) <= 0 And TextLen > 0 Then
strWhere = strText & " AND "
Else
strWhere = strWhere
End If

'Add quotes and astricts to make any, all, or exact phrase
strings from description.
Dim strDescription As String, strAny As String, strAll As String, strAst
As String

strAst = "*"
strAny = strAst & strQ & " OR [Description] Like " & strQ & strAst
strAll = strAst & strQ & " AND [Description] Like " & strQ & strAst

If Not IsNull(Me.fDescription) Then
strDescription = Me.fDescription
'Debug.Print strDescription
If (Me.optDesc) = 1 Then
strDescription = Replace(strDescription, " ", strAny)
strWhere = strWhere & "([Description] Like ""*" & strDescription &
"*"") AND "
ElseIf (Me.optDesc) = 2 Then
strDescription = Replace(strDescription, " ", strAll)
strWhere = strWhere & "([Description] Like ""*" & strDescription &
"*"") AND "
ElseIf (Me.optDesc) = 3 Then
strWhere = strWhere & "([Description] Like ""*" & strDescription &
"*"") AND "
'Debug.Print strWhere
End If

End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 And IsNull(Me.fcboCnd1) And IsNull(Me.fcboCnd2) And
IsNull(Me.fcboCnd3) Then
MsgBox "No Criteria", vbInformation, "Nothing to do."
ElseIf lngLen > 0 Then
Call Reset(Me)
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
Me.FilterString.ForeColor = vbBlack
End If

On Error GoTo Err_cmdFilterAnd_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70

Exit_cmdFilterAnd_Click:
Exit Sub

Err_cmdFilterAnd_Click:
MsgBox Err.Description
Resume Exit_cmdFilterAnd_Click

End Sub

Douglas J. Steele said:
The code you've presented is syntactically correct (which is to be expected
from Allen's work).

How are you using that code? How are you determining that nothing's being
returned?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

bymarce said:

I have a filter form (based on Allen Browne's code). This portion of the
code is supposed to build a string based on a multiselect list box but
it's
returning nothing no matter how many items are selected. Why isn't this
working? Thanks.
Marcie
'Loop through the ItemsSelected in the list box.
Dim strProperty As String, PropertyLen As Long, varItem As Variant,
strQ As String
strQ = """"
With Me.fProperty
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strProperty = strProperty & strQ & .Column(0, varItem)
&
strQ & ", "
End If
Next
End With
Debug.Print strProperty

PropertyLen = Len(strProperty) - 2
Debug.Print PropertyLen

If PropertyLen > 0 Then
strProperty = "([Property] IN (" & Left$(strProperty,
PropertyLen) & "))"
strWhere = strWhere & strProperty & " AND "
End If

Click to expand...

Douglas J. Steele · Sep 17, 2009

Sorry, I only had time for a really quick look at the moment (hopefully I'll
get some more time this evening), but in the code abourt fProperty, you're
using

strProperty = strProperty & strQ & .Column(varItem) & strQ & ", "

That should be either

strProperty = strProperty & strQ & .ItemData(varItem) & strQ & ", "

(if you're trying to get the value of the bound column)

or

strProperty = strProperty & strQ & .Column(n, varItem) & strQ & ", "

(where n is the number of the column, 0-based, whose value you're trying to
get)

However, it sounds as though it's not doing anything with your code if
strProperty isn't gettting any values in it. Before the With Me.fProperty
statement, try putting

MsgBox "There are " & Me!fProperty.ItemsSelected.Count & " items selected in
listbox fProperty."

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

bymarce said:
Here's the entire code for this button. I have about 20 unbound controls
(combo, list, and text boxes) in the header of the form for filtering
experimental data based on 3 tables: MLOBOOK (sample catalog), DATA, and
Conditions. I based this on Allen's filter form example and filtering
based
on a field in a subform. When I select only values in fProperty my form
pops
up a message that says "No Criteria". Also debug.print PropertyLen
returns -2
in the immediate window. I'm also trying to have this button add to the
filter string using "OR" if the form is already filtered rather than
starting
from scratch every time. Thanks for your help.
Marcie

Private Sub cmdFilterAnd_Click() 'Adapted from
http://allenbrowne.com/ser-62code.html

Dim segCND As Long
Dim segNum As Long
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
segNum = 0
Dim strCND As String
Dim lenCND As Long
If Me.FilterOn = True Then
strWhere = Me.Filter
strWhere = "(" & strWhere & ") OR ("
segNum = 1
'Debug.Print strWhere
End If

strCND = Me.RecordSource
lenCND = Len(strCND)
'Debug.Print lenCND
If lenCND > 654 Then
strCND = Right(strCND, (lenCND - 654))
strCND = Left(strCND, Len(strCND) - 3)
'Debug.Print strCND
strCND = "(" & strCND & ") OR ("
segCND = 1
Else
strCND = ""
segCND = 0
End If

Dim strSQL As String
If IsNull(Me.fcboCnd1) And segCND = 0 Then
' If the combo is Null, use the whole table as the
RecordSource.
Me.RecordSource = "SELECT MLOBOOK.Description,
Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM MLOBOOK INNER JOIN Data ON (MLOBOOK.CatalogID =
Data.CatalogID) AND (MLOBOOK.CatalogYear = Data.CatalogYear) " & _
"AND (MLOBOOK.CatalogPrefix = Data.CatalogPrefix) WHERE
(((Data.CatalogYear) Is Not Null));"
Else
If Not IsNull(Me.fcboCnd1) Then
strCND = strCND & "((Conditions.ConditionName) Like """ &
Me.fcboCnd1 & """) And "
If Not IsNull(Me.fCnd1L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd1L
& ") And "
End If
If Not IsNull(Me.fCnd1H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd1H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits1) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits1 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd2) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd2
& """) And "
If Not IsNull(Me.fCnd2L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd2L
& ") And "
End If
If Not IsNull(Me.fCnd2H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd2H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits2) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits2 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd3) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd3
& """) And "
If Not IsNull(Me.fCnd3L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd3L
& ") And "
End If
If Not IsNull(Me.fCnd3H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd3H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits3) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits3 & """) And "
End If
End If

lenCND = Len(strCND) - 5
strCND = Left(strCND, lenCND)
'Debug.Print strCND
If segCND = 1 Then
strCND = strCND & ")"
End If
strSQL = "SELECT MLOBOOK.Description, Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM Conditions INNER JOIN ((MLOBOOK INNER JOIN Data ON
(MLOBOOK.CatalogID = Data.CatalogID) AND (MLOBOOK.CatalogYear " & _
"= Data.CatalogYear) AND (MLOBOOK.CatalogPrefix =
Data.CatalogPrefix)) INNER JOIN ConditionsDataJunction " & _
"ON Data.ID = ConditionsDataJunction.DataID) ON Conditions.ID =
ConditionsDataJunction.ConditionsID " & _
"WHERE (((Data.CatalogYear) Is Not Null) AND (" & strCND &
"));"
'Debug.Print strSQL
Me.RecordSource = strSQL
End If

Dim strPrefix As String
Dim strYear As String
Dim strID As String

If Not IsNull(Me.cboMLOL) And IsNull(Me.cboMLOH) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And Not IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) and "
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) and "
End If
'Debug.Print strWhere

'Loop through the ItemsSelected in the list box.
Dim strProperty As String, PropertyLen As Long, varItem As Variant,
strQ As String
strQ = """"
With Me.fProperty
For Each varItem In .ItemsSelected
'Build up the filter from the bound column (hidden).
strProperty = strProperty & strQ & .Column(varItem) &
strQ & ", "
Next varItem
End With
Debug.Print strProperty

PropertyLen = Len(strProperty) - 2
Debug.Print PropertyLen

If PropertyLen > 0 Then
strProperty = "([Property] IN (" & Left$(strProperty,
PropertyLen) & "))"
strWhere = strWhere & strProperty & " AND "
End If
'Remove trailing comma. Add field name, IN operator, and
brackets.

'Loop through the ItemsSelected in the list box.
Dim strMethod As String, methodlen As Long
With Me.fMethod
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strMethod = strMethod & strQ & .ItemData(varItem) & strQ &
","
End If
Next
End With

methodlen = Len(strMethod) - 1
If methodlen > 0 Then
strMethod = "[TestMethod] IN (" & Left$(strMethod, methodlen) &
")"
End If
If Len(strWhere) > 0 And methodlen > 0 Then
strWhere = strWhere & "(" & strMethod & ") AND "
ElseIf Len(strWhere) <= 0 And methodlen > 0 Then
strWhere = strMethod & " AND "
Else
strWhere = strWhere
End If

'Remove trailing comma. Add field name, IN operator, and
brackets.
'Deselect and clear filter controls.

If Not IsNull(Me.fResultL) Then
strWhere = strWhere & "([RNumeric] >= " & Me.fResultL & ") AND
"
End If

If Not IsNull(Me.fResultH) Then
strWhere = strWhere & "([RNumeric] <= " & Me.fResultH & ") AND
"
End If

'Loop through the ItemsSelected in the list box.
Dim strText As String, TextLen As Long
With Me.fResultText
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strText = strText & strQ & .ItemData(varItem) & strQ &
","
End If
Next
End With

TextLen = Len(strText) - 1
If TextLen > 0 Then
strText = "[RText] IN (" & Left$(strText, TextLen) & ")"
End If
If Len(strWhere) > 0 And TextLen > 0 Then
strWhere = strWhere & "(" & strText & ") AND "
ElseIf Len(strWhere) <= 0 And TextLen > 0 Then
strWhere = strText & " AND "
Else
strWhere = strWhere
End If

'Add quotes and astricts to make any, all, or exact phrase
strings from description.
Dim strDescription As String, strAny As String, strAll As String,
strAst
As String

strAst = "*"
strAny = strAst & strQ & " OR [Description] Like " & strQ & strAst
strAll = strAst & strQ & " AND [Description] Like " & strQ & strAst

If Not IsNull(Me.fDescription) Then
strDescription = Me.fDescription
'Debug.Print strDescription
If (Me.optDesc) = 1 Then
strDescription = Replace(strDescription, " ", strAny)
strWhere = strWhere & "([Description] Like ""*" & strDescription &
"*"") AND "
ElseIf (Me.optDesc) = 2 Then
strDescription = Replace(strDescription, " ", strAll)
strWhere = strWhere & "([Description] Like ""*" & strDescription &
"*"") AND "
ElseIf (Me.optDesc) = 3 Then
strWhere = strWhere & "([Description] Like ""*" & strDescription &
"*"") AND "
'Debug.Print strWhere
End If

End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 And IsNull(Me.fcboCnd1) And IsNull(Me.fcboCnd2) And
IsNull(Me.fcboCnd3) Then
MsgBox "No Criteria", vbInformation, "Nothing to do."
ElseIf lngLen > 0 Then
Call Reset(Me)
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
Me.FilterString.ForeColor = vbBlack
End If

On Error GoTo Err_cmdFilterAnd_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70

Exit_cmdFilterAnd_Click:
Exit Sub

Err_cmdFilterAnd_Click:
MsgBox Err.Description
Resume Exit_cmdFilterAnd_Click

End Sub

Douglas J. Steele said:

The code you've presented is syntactically correct (which is to be
expected
from Allen's work).

How are you using that code? How are you determining that nothing's being
returned?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

bymarce said:

I have a filter form (based on Allen Browne's code). This portion of
the
code is supposed to build a string based on a multiselect list box but
it's
returning nothing no matter how many items are selected. Why isn't
this
working? Thanks.
Marcie
'Loop through the ItemsSelected in the list box.
Dim strProperty As String, PropertyLen As Long, varItem As
Variant,
strQ As String
strQ = """"
With Me.fProperty
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strProperty = strProperty & strQ & .Column(0,
varItem)
&
strQ & ", "
End If
Next
End With
Debug.Print strProperty

PropertyLen = Len(strProperty) - 2
Debug.Print PropertyLen

If PropertyLen > 0 Then
strProperty = "([Property] IN (" & Left$(strProperty,
PropertyLen) & "))"
strWhere = strWhere & strProperty & " AND "
End If

Click to expand...

Click to expand...

bymarce · Sep 17, 2009

Thanks for the tip. I put in the msgbox code and it says there are 0 items.
So then I put it at the begining and it said there are 2 selected. I'm going
to move it down until I figure out where the problem is.

Douglas J. Steele said:
Sorry, I only had time for a really quick look at the moment (hopefully I'll
get some more time this evening), but in the code abourt fProperty, you're
using

strProperty = strProperty & strQ & .Column(varItem) & strQ & ", "

That should be either

strProperty = strProperty & strQ & .ItemData(varItem) & strQ & ", "

(if you're trying to get the value of the bound column)

or

strProperty = strProperty & strQ & .Column(n, varItem) & strQ & ", "

(where n is the number of the column, 0-based, whose value you're trying to
get)

However, it sounds as though it's not doing anything with your code if
strProperty isn't gettting any values in it. Before the With Me.fProperty
statement, try putting

MsgBox "There are " & Me!fProperty.ItemsSelected.Count & " items selected in
listbox fProperty."

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

bymarce said:

Here's the entire code for this button. I have about 20 unbound controls
(combo, list, and text boxes) in the header of the form for filtering
experimental data based on 3 tables: MLOBOOK (sample catalog), DATA, and
Conditions. I based this on Allen's filter form example and filtering
based
on a field in a subform. When I select only values in fProperty my form
pops
up a message that says "No Criteria". Also debug.print PropertyLen
returns -2
in the immediate window. I'm also trying to have this button add to the
filter string using "OR" if the form is already filtered rather than
starting
from scratch every time. Thanks for your help.
Marcie

Private Sub cmdFilterAnd_Click() 'Adapted from
http://allenbrowne.com/ser-62code.html

Dim segCND As Long
Dim segNum As Long
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
segNum = 0
Dim strCND As String
Dim lenCND As Long
If Me.FilterOn = True Then
strWhere = Me.Filter
strWhere = "(" & strWhere & ") OR ("
segNum = 1
'Debug.Print strWhere
End If

strCND = Me.RecordSource
lenCND = Len(strCND)
'Debug.Print lenCND
If lenCND > 654 Then
strCND = Right(strCND, (lenCND - 654))
strCND = Left(strCND, Len(strCND) - 3)
'Debug.Print strCND
strCND = "(" & strCND & ") OR ("
segCND = 1
Else
strCND = ""
segCND = 0
End If

Dim strSQL As String
If IsNull(Me.fcboCnd1) And segCND = 0 Then
' If the combo is Null, use the whole table as the
RecordSource.
Me.RecordSource = "SELECT MLOBOOK.Description,
Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM MLOBOOK INNER JOIN Data ON (MLOBOOK.CatalogID =
Data.CatalogID) AND (MLOBOOK.CatalogYear = Data.CatalogYear) " & _
"AND (MLOBOOK.CatalogPrefix = Data.CatalogPrefix) WHERE
(((Data.CatalogYear) Is Not Null));"
Else
If Not IsNull(Me.fcboCnd1) Then
strCND = strCND & "((Conditions.ConditionName) Like """ &
Me.fcboCnd1 & """) And "
If Not IsNull(Me.fCnd1L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd1L
& ") And "
End If
If Not IsNull(Me.fCnd1H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd1H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits1) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits1 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd2) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd2
& """) And "
If Not IsNull(Me.fCnd2L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd2L
& ") And "
End If
If Not IsNull(Me.fCnd2H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd2H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits2) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits2 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd3) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd3
& """) And "
If Not IsNull(Me.fCnd3L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd3L
& ") And "
End If
If Not IsNull(Me.fCnd3H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd3H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits3) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits3 & """) And "
End If
End If

lenCND = Len(strCND) - 5
strCND = Left(strCND, lenCND)
'Debug.Print strCND
If segCND = 1 Then
strCND = strCND & ")"
End If
strSQL = "SELECT MLOBOOK.Description, Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM Conditions INNER JOIN ((MLOBOOK INNER JOIN Data ON
(MLOBOOK.CatalogID = Data.CatalogID) AND (MLOBOOK.CatalogYear " & _
"= Data.CatalogYear) AND (MLOBOOK.CatalogPrefix =
Data.CatalogPrefix)) INNER JOIN ConditionsDataJunction " & _
"ON Data.ID = ConditionsDataJunction.DataID) ON Conditions.ID =
ConditionsDataJunction.ConditionsID " & _
"WHERE (((Data.CatalogYear) Is Not Null) AND (" & strCND &
"));"
'Debug.Print strSQL
Me.RecordSource = strSQL
End If

Dim strPrefix As String
Dim strYear As String
Dim strID As String

If Not IsNull(Me.cboMLOL) And IsNull(Me.cboMLOH) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And Not IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) and "
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) and "
End If
'Debug.Print strWhere

'Loop through the ItemsSelected in the list box.
Dim strProperty As String, PropertyLen As Long, varItem As Variant,
strQ As String
strQ = """"
With Me.fProperty
For Each varItem In .ItemsSelected
'Build up the filter from the bound column (hidden).
strProperty = strProperty & strQ & .Column(varItem) &
strQ & ", "
Next varItem
End With
Debug.Print strProperty

PropertyLen = Len(strProperty) - 2
Debug.Print PropertyLen

If PropertyLen > 0 Then
strProperty = "([Property] IN (" & Left$(strProperty,
PropertyLen) & "))"
strWhere = strWhere & strProperty & " AND "
End If
'Remove trailing comma. Add field name, IN operator, and
brackets.

'Loop through the ItemsSelected in the list box.
Dim strMethod As String, methodlen As Long
With Me.fMethod
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strMethod = strMethod & strQ & .ItemData(varItem) & strQ &
","
End If
Next
End With

methodlen = Len(strMethod) - 1
If methodlen > 0 Then
strMethod = "[TestMethod] IN (" & Left$(strMethod, methodlen) &
")"
End If
If Len(strWhere) > 0 And methodlen > 0 Then
strWhere = strWhere & "(" & strMethod & ") AND "
ElseIf Len(strWhere) <= 0 And methodlen > 0 Then
strWhere = strMethod & " AND "
Else
strWhere = strWhere
End If

'Remove trailing comma. Add field name, IN operator, and
brackets.

Click to expand...

bymarce · Sep 17, 2009

When I put the msgbox you suggested before "If IsNull(Me.fcboCnd1) And segCND
= 0 Then" it returns the correct number of selected items. When it's after
this line it returns 0. I guess it's resetting the list box when I change
the recordsource of the form in the code. I don't know why though. Thanks
for your help.

Douglas J. Steele said:
Sorry, I only had time for a really quick look at the moment (hopefully I'll
get some more time this evening), but in the code abourt fProperty, you're
using

strProperty = strProperty & strQ & .Column(varItem) & strQ & ", "

That should be either

strProperty = strProperty & strQ & .ItemData(varItem) & strQ & ", "

(if you're trying to get the value of the bound column)

or

strProperty = strProperty & strQ & .Column(n, varItem) & strQ & ", "

(where n is the number of the column, 0-based, whose value you're trying to
get)

However, it sounds as though it's not doing anything with your code if
strProperty isn't gettting any values in it. Before the With Me.fProperty
statement, try putting

MsgBox "There are " & Me!fProperty.ItemsSelected.Count & " items selected in
listbox fProperty."

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

bymarce said:

Here's the entire code for this button. I have about 20 unbound controls
(combo, list, and text boxes) in the header of the form for filtering
experimental data based on 3 tables: MLOBOOK (sample catalog), DATA, and
Conditions. I based this on Allen's filter form example and filtering
based
on a field in a subform. When I select only values in fProperty my form
pops
up a message that says "No Criteria". Also debug.print PropertyLen
returns -2
in the immediate window. I'm also trying to have this button add to the
filter string using "OR" if the form is already filtered rather than
starting
from scratch every time. Thanks for your help.
Marcie

Private Sub cmdFilterAnd_Click() 'Adapted from
http://allenbrowne.com/ser-62code.html

Dim segCND As Long
Dim segNum As Long
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
segNum = 0
Dim strCND As String
Dim lenCND As Long
If Me.FilterOn = True Then
strWhere = Me.Filter
strWhere = "(" & strWhere & ") OR ("
segNum = 1
'Debug.Print strWhere
End If

strCND = Me.RecordSource
lenCND = Len(strCND)
'Debug.Print lenCND
If lenCND > 654 Then
strCND = Right(strCND, (lenCND - 654))
strCND = Left(strCND, Len(strCND) - 3)
'Debug.Print strCND
strCND = "(" & strCND & ") OR ("
segCND = 1
Else
strCND = ""
segCND = 0
End If

Dim strSQL As String
If IsNull(Me.fcboCnd1) And segCND = 0 Then
' If the combo is Null, use the whole table as the
RecordSource.
Me.RecordSource = "SELECT MLOBOOK.Description,
Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM MLOBOOK INNER JOIN Data ON (MLOBOOK.CatalogID =
Data.CatalogID) AND (MLOBOOK.CatalogYear = Data.CatalogYear) " & _
"AND (MLOBOOK.CatalogPrefix = Data.CatalogPrefix) WHERE
(((Data.CatalogYear) Is Not Null));"
Else
If Not IsNull(Me.fcboCnd1) Then
strCND = strCND & "((Conditions.ConditionName) Like """ &
Me.fcboCnd1 & """) And "
If Not IsNull(Me.fCnd1L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd1L
& ") And "
End If
If Not IsNull(Me.fCnd1H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd1H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits1) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits1 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd2) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd2
& """) And "
If Not IsNull(Me.fCnd2L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd2L
& ") And "
End If
If Not IsNull(Me.fCnd2H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd2H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits2) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits2 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd3) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd3
& """) And "
If Not IsNull(Me.fCnd3L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd3L
& ") And "
End If
If Not IsNull(Me.fCnd3H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd3H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits3) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits3 & """) And "
End If
End If

lenCND = Len(strCND) - 5
strCND = Left(strCND, lenCND)
'Debug.Print strCND
If segCND = 1 Then
strCND = strCND & ")"
End If
strSQL = "SELECT MLOBOOK.Description, Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM Conditions INNER JOIN ((MLOBOOK INNER JOIN Data ON
(MLOBOOK.CatalogID = Data.CatalogID) AND (MLOBOOK.CatalogYear " & _
"= Data.CatalogYear) AND (MLOBOOK.CatalogPrefix =
Data.CatalogPrefix)) INNER JOIN ConditionsDataJunction " & _
"ON Data.ID = ConditionsDataJunction.DataID) ON Conditions.ID =
ConditionsDataJunction.ConditionsID " & _
"WHERE (((Data.CatalogYear) Is Not Null) AND (" & strCND &
"));"
'Debug.Print strSQL
Me.RecordSource = strSQL
End If

Dim strPrefix As String
Dim strYear As String
Dim strID As String

If Not IsNull(Me.cboMLOL) And IsNull(Me.cboMLOH) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And Not IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) and "
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) and "
End If
'Debug.Print strWhere

'Loop through the ItemsSelected in the list box.
Dim strProperty As String, PropertyLen As Long, varItem As Variant,
strQ As String
strQ = """"
With Me.fProperty
For Each varItem In .ItemsSelected
'Build up the filter from the bound column (hidden).
strProperty = strProperty & strQ & .Column(varItem) &
strQ & ", "
Next varItem
End With
Debug.Print strProperty

PropertyLen = Len(strProperty) - 2
Debug.Print PropertyLen

If PropertyLen > 0 Then
strProperty = "([Property] IN (" & Left$(strProperty,
PropertyLen) & "))"
strWhere = strWhere & strProperty & " AND "
End If
'Remove trailing comma. Add field name, IN operator, and
brackets.

'Loop through the ItemsSelected in the list box.
Dim strMethod As String, methodlen As Long
With Me.fMethod
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strMethod = strMethod & strQ & .ItemData(varItem) & strQ &
","
End If
Next
End With

methodlen = Len(strMethod) - 1
If methodlen > 0 Then
strMethod = "[TestMethod] IN (" & Left$(strMethod, methodlen) &
")"
End If
If Len(strWhere) > 0 And methodlen > 0 Then
strWhere = strWhere & "(" & strMethod & ") AND "
ElseIf Len(strWhere) <= 0 And methodlen > 0 Then
strWhere = strMethod & " AND "
Else
strWhere = strWhere
End If

'Remove trailing comma. Add field name, IN operator, and
brackets.

Click to expand...

bymarce · Sep 17, 2009

Thanks for your help. I've got it working now. I just moved the code that
changes the recordsource to be after the code that builds the filter string.
Marcie

bymarce said:
When I put the msgbox you suggested before "If IsNull(Me.fcboCnd1) And segCND
= 0 Then" it returns the correct number of selected items. When it's after
this line it returns 0. I guess it's resetting the list box when I change
the recordsource of the form in the code. I don't know why though. Thanks
for your help.

Douglas J. Steele said:

Sorry, I only had time for a really quick look at the moment (hopefully I'll
get some more time this evening), but in the code abourt fProperty, you're
using

strProperty = strProperty & strQ & .Column(varItem) & strQ & ", "

That should be either

strProperty = strProperty & strQ & .ItemData(varItem) & strQ & ", "

(if you're trying to get the value of the bound column)

or

strProperty = strProperty & strQ & .Column(n, varItem) & strQ & ", "

(where n is the number of the column, 0-based, whose value you're trying to
get)

However, it sounds as though it's not doing anything with your code if
strProperty isn't gettting any values in it. Before the With Me.fProperty
statement, try putting

MsgBox "There are " & Me!fProperty.ItemsSelected.Count & " items selected in
listbox fProperty."

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

bymarce said:

Here's the entire code for this button. I have about 20 unbound controls
(combo, list, and text boxes) in the header of the form for filtering
experimental data based on 3 tables: MLOBOOK (sample catalog), DATA, and
Conditions. I based this on Allen's filter form example and filtering
based
on a field in a subform. When I select only values in fProperty my form
pops
up a message that says "No Criteria". Also debug.print PropertyLen
returns -2
in the immediate window. I'm also trying to have this button add to the
filter string using "OR" if the form is already filtered rather than
starting
from scratch every time. Thanks for your help.
Marcie

Private Sub cmdFilterAnd_Click() 'Adapted from
http://allenbrowne.com/ser-62code.html

Dim segCND As Long
Dim segNum As Long
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
segNum = 0
Dim strCND As String
Dim lenCND As Long
If Me.FilterOn = True Then
strWhere = Me.Filter
strWhere = "(" & strWhere & ") OR ("
segNum = 1
'Debug.Print strWhere
End If

strCND = Me.RecordSource
lenCND = Len(strCND)
'Debug.Print lenCND
If lenCND > 654 Then
strCND = Right(strCND, (lenCND - 654))
strCND = Left(strCND, Len(strCND) - 3)
'Debug.Print strCND
strCND = "(" & strCND & ") OR ("
segCND = 1
Else
strCND = ""
segCND = 0
End If

Dim strSQL As String
If IsNull(Me.fcboCnd1) And segCND = 0 Then
' If the combo is Null, use the whole table as the
RecordSource.
Me.RecordSource = "SELECT MLOBOOK.Description,
Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM MLOBOOK INNER JOIN Data ON (MLOBOOK.CatalogID =
Data.CatalogID) AND (MLOBOOK.CatalogYear = Data.CatalogYear) " & _
"AND (MLOBOOK.CatalogPrefix = Data.CatalogPrefix) WHERE
(((Data.CatalogYear) Is Not Null));"
Else
If Not IsNull(Me.fcboCnd1) Then
strCND = strCND & "((Conditions.ConditionName) Like """ &
Me.fcboCnd1 & """) And "
If Not IsNull(Me.fCnd1L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd1L
& ") And "
End If
If Not IsNull(Me.fCnd1H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd1H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits1) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits1 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd2) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd2
& """) And "
If Not IsNull(Me.fCnd2L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd2L
& ") And "
End If
If Not IsNull(Me.fCnd2H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd2H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits2) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits2 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd3) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd3
& """) And "
If Not IsNull(Me.fCnd3L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd3L
& ") And "
End If
If Not IsNull(Me.fCnd3H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd3H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits3) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits3 & """) And "
End If
End If

lenCND = Len(strCND) - 5
strCND = Left(strCND, lenCND)
'Debug.Print strCND
If segCND = 1 Then
strCND = strCND & ")"
End If
strSQL = "SELECT MLOBOOK.Description, Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM Conditions INNER JOIN ((MLOBOOK INNER JOIN Data ON
(MLOBOOK.CatalogID = Data.CatalogID) AND (MLOBOOK.CatalogYear " & _
"= Data.CatalogYear) AND (MLOBOOK.CatalogPrefix =
Data.CatalogPrefix)) INNER JOIN ConditionsDataJunction " & _
"ON Data.ID = ConditionsDataJunction.DataID) ON Conditions.ID =
ConditionsDataJunction.ConditionsID " & _
"WHERE (((Data.CatalogYear) Is Not Null) AND (" & strCND &
"));"
'Debug.Print strSQL
Me.RecordSource = strSQL
End If

Dim strPrefix As String
Dim strYear As String
Dim strID As String

If Not IsNull(Me.cboMLOL) And IsNull(Me.cboMLOH) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And Not IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) and "
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) and "
End If
'Debug.Print strWhere

'Loop through the ItemsSelected in the list box.
Dim strProperty As String, PropertyLen As Long, varItem As Variant,
strQ As String
strQ = """"
With Me.fProperty
For Each varItem In .ItemsSelected
'Build up the filter from the bound column (hidden).
strProperty = strProperty & strQ & .Column(varItem) &
strQ & ", "
Next varItem
End With
Debug.Print strProperty

PropertyLen = Len(strProperty) - 2
Debug.Print PropertyLen

If PropertyLen > 0 Then
strProperty = "([Property] IN (" & Left$(strProperty,
PropertyLen) & "))"
strWhere = strWhere & strProperty & " AND "
End If
'Remove trailing comma. Add field name, IN operator, and
brackets.

'Loop through the ItemsSelected in the list box.
Dim strMethod As String, methodlen As Long
With Me.fMethod
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strMethod = strMethod & strQ & .ItemData(varItem) & strQ &
","
End If
Next
End With

methodlen = Len(strMethod) - 1
If methodlen > 0 Then
strMethod = "[TestMethod] IN (" & Left$(strMethod, methodlen) &
")"
End If
If Len(strWhere) > 0 And methodlen > 0 Then
strWhere = strWhere & "(" & strMethod & ") AND "

Click to expand...

Click to expand...

bymarce · Sep 18, 2009

Hi again. I thought I had this worked out but when I put the code for
builidng a string from the list box before the code that changes the record
source, the record source won't change. Why are these coflicting?
Marcie

Douglas J. Steele said:
Sorry, I only had time for a really quick look at the moment (hopefully I'll
get some more time this evening), but in the code abourt fProperty, you're
using

strProperty = strProperty & strQ & .Column(varItem) & strQ & ", "

That should be either

strProperty = strProperty & strQ & .ItemData(varItem) & strQ & ", "

(if you're trying to get the value of the bound column)

or

strProperty = strProperty & strQ & .Column(n, varItem) & strQ & ", "

(where n is the number of the column, 0-based, whose value you're trying to
get)

However, it sounds as though it's not doing anything with your code if
strProperty isn't gettting any values in it. Before the With Me.fProperty
statement, try putting

MsgBox "There are " & Me!fProperty.ItemsSelected.Count & " items selected in
listbox fProperty."

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

bymarce said:

Here's the entire code for this button. I have about 20 unbound controls
(combo, list, and text boxes) in the header of the form for filtering
experimental data based on 3 tables: MLOBOOK (sample catalog), DATA, and
Conditions. I based this on Allen's filter form example and filtering
based
on a field in a subform. When I select only values in fProperty my form
pops
up a message that says "No Criteria". Also debug.print PropertyLen
returns -2
in the immediate window. I'm also trying to have this button add to the
filter string using "OR" if the form is already filtered rather than
starting
from scratch every time. Thanks for your help.
Marcie

Private Sub cmdFilterAnd_Click() 'Adapted from
http://allenbrowne.com/ser-62code.html

Dim segCND As Long
Dim segNum As Long
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
segNum = 0
Dim strCND As String
Dim lenCND As Long
If Me.FilterOn = True Then
strWhere = Me.Filter
strWhere = "(" & strWhere & ") OR ("
segNum = 1
'Debug.Print strWhere
End If

strCND = Me.RecordSource
lenCND = Len(strCND)
'Debug.Print lenCND
If lenCND > 654 Then
strCND = Right(strCND, (lenCND - 654))
strCND = Left(strCND, Len(strCND) - 3)
'Debug.Print strCND
strCND = "(" & strCND & ") OR ("
segCND = 1
Else
strCND = ""
segCND = 0
End If

Dim strSQL As String
If IsNull(Me.fcboCnd1) And segCND = 0 Then
' If the combo is Null, use the whole table as the
RecordSource.
Me.RecordSource = "SELECT MLOBOOK.Description,
Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM MLOBOOK INNER JOIN Data ON (MLOBOOK.CatalogID =
Data.CatalogID) AND (MLOBOOK.CatalogYear = Data.CatalogYear) " & _
"AND (MLOBOOK.CatalogPrefix = Data.CatalogPrefix) WHERE
(((Data.CatalogYear) Is Not Null));"
Else
If Not IsNull(Me.fcboCnd1) Then
strCND = strCND & "((Conditions.ConditionName) Like """ &
Me.fcboCnd1 & """) And "
If Not IsNull(Me.fCnd1L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd1L
& ") And "
End If
If Not IsNull(Me.fCnd1H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd1H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits1) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits1 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd2) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd2
& """) And "
If Not IsNull(Me.fCnd2L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd2L
& ") And "
End If
If Not IsNull(Me.fCnd2H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd2H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits2) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits2 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd3) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd3
& """) And "
If Not IsNull(Me.fCnd3L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd3L
& ") And "
End If
If Not IsNull(Me.fCnd3H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd3H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits3) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits3 & """) And "
End If
End If

lenCND = Len(strCND) - 5
strCND = Left(strCND, lenCND)
'Debug.Print strCND
If segCND = 1 Then
strCND = strCND & ")"
End If
strSQL = "SELECT MLOBOOK.Description, Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM Conditions INNER JOIN ((MLOBOOK INNER JOIN Data ON
(MLOBOOK.CatalogID = Data.CatalogID) AND (MLOBOOK.CatalogYear " & _
"= Data.CatalogYear) AND (MLOBOOK.CatalogPrefix =
Data.CatalogPrefix)) INNER JOIN ConditionsDataJunction " & _
"ON Data.ID = ConditionsDataJunction.DataID) ON Conditions.ID =
ConditionsDataJunction.ConditionsID " & _
"WHERE (((Data.CatalogYear) Is Not Null) AND (" & strCND &
"));"
'Debug.Print strSQL
Me.RecordSource = strSQL
End If

Dim strPrefix As String
Dim strYear As String
Dim strID As String

If Not IsNull(Me.cboMLOL) And IsNull(Me.cboMLOH) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And Not IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) and "
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) and "
End If
'Debug.Print strWhere

'Loop through the ItemsSelected in the list box.
Dim strProperty As String, PropertyLen As Long, varItem As Variant,
strQ As String
strQ = """"
With Me.fProperty
For Each varItem In .ItemsSelected
'Build up the filter from the bound column (hidden).
strProperty = strProperty & strQ & .Column(varItem) &
strQ & ", "
Next varItem
End With
Debug.Print strProperty

PropertyLen = Len(strProperty) - 2
Debug.Print PropertyLen

If PropertyLen > 0 Then
strProperty = "([Property] IN (" & Left$(strProperty,
PropertyLen) & "))"
strWhere = strWhere & strProperty & " AND "
End If
'Remove trailing comma. Add field name, IN operator, and
brackets.

'Loop through the ItemsSelected in the list box.
Dim strMethod As String, methodlen As Long
With Me.fMethod
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strMethod = strMethod & strQ & .ItemData(varItem) & strQ &
","
End If
Next
End With

methodlen = Len(strMethod) - 1
If methodlen > 0 Then
strMethod = "[TestMethod] IN (" & Left$(strMethod, methodlen) &
")"
End If
If Len(strWhere) > 0 And methodlen > 0 Then
strWhere = strWhere & "(" & strMethod & ") AND "
ElseIf Len(strWhere) <= 0 And methodlen > 0 Then
strWhere = strMethod & " AND "
Else
strWhere = strWhere
End If

'Remove trailing comma. Add field name, IN operator, and
brackets.

Click to expand...

Douglas J. Steele · Sep 18, 2009

What do you mean by "the record source won't change"? Are you getting an
error? If so, what's the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

bymarce said:
Hi again. I thought I had this worked out but when I put the code for
builidng a string from the list box before the code that changes the
record
source, the record source won't change. Why are these coflicting?
Marcie

Douglas J. Steele said:

Sorry, I only had time for a really quick look at the moment (hopefully
I'll
get some more time this evening), but in the code abourt fProperty,
you're
using

strProperty = strProperty & strQ & .Column(varItem) & strQ & ", "

That should be either

strProperty = strProperty & strQ & .ItemData(varItem) & strQ & ", "

(if you're trying to get the value of the bound column)

or

strProperty = strProperty & strQ & .Column(n, varItem) & strQ & ", "

(where n is the number of the column, 0-based, whose value you're trying
to
get)

However, it sounds as though it's not doing anything with your code if
strProperty isn't gettting any values in it. Before the With Me.fProperty
statement, try putting

MsgBox "There are " & Me!fProperty.ItemsSelected.Count & " items selected
in
listbox fProperty."

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

bymarce said:

Here's the entire code for this button. I have about 20 unbound
controls
(combo, list, and text boxes) in the header of the form for filtering
experimental data based on 3 tables: MLOBOOK (sample catalog), DATA,
and
Conditions. I based this on Allen's filter form example and filtering
based
on a field in a subform. When I select only values in fProperty my
form
pops
up a message that says "No Criteria". Also debug.print PropertyLen
returns -2
in the immediate window. I'm also trying to have this button add to the
filter string using "OR" if the form is already filtered rather than
starting
from scratch every time. Thanks for your help.
Marcie

Private Sub cmdFilterAnd_Click() 'Adapted from
http://allenbrowne.com/ser-62code.html

Dim segCND As Long
Dim segNum As Long
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
segNum = 0
Dim strCND As String
Dim lenCND As Long
If Me.FilterOn = True Then
strWhere = Me.Filter
strWhere = "(" & strWhere & ") OR ("
segNum = 1
'Debug.Print strWhere
End If

strCND = Me.RecordSource
lenCND = Len(strCND)
'Debug.Print lenCND
If lenCND > 654 Then
strCND = Right(strCND, (lenCND - 654))
strCND = Left(strCND, Len(strCND) - 3)
'Debug.Print strCND
strCND = "(" & strCND & ") OR ("
segCND = 1
Else
strCND = ""
segCND = 0
End If

Dim strSQL As String
If IsNull(Me.fcboCnd1) And segCND = 0 Then
' If the combo is Null, use the whole table as the
RecordSource.
Me.RecordSource = "SELECT MLOBOOK.Description,
Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM MLOBOOK INNER JOIN Data ON (MLOBOOK.CatalogID =
Data.CatalogID) AND (MLOBOOK.CatalogYear = Data.CatalogYear) " & _
"AND (MLOBOOK.CatalogPrefix = Data.CatalogPrefix) WHERE
(((Data.CatalogYear) Is Not Null));"
Else
If Not IsNull(Me.fcboCnd1) Then
strCND = strCND & "((Conditions.ConditionName) Like """
&
Me.fcboCnd1 & """) And "
If Not IsNull(Me.fCnd1L) Then
strCND = strCND & "((Conditions.Value) >= " &
Me.fCnd1L
& ") And "
End If
If Not IsNull(Me.fCnd1H) Then
strCND = strCND & "((Conditions.Value) <= " &
Me.fCnd1H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits1) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits1 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd2) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd2
& """) And "
If Not IsNull(Me.fCnd2L) Then
strCND = strCND & "((Conditions.Value) >= " &
Me.fCnd2L
& ") And "
End If
If Not IsNull(Me.fCnd2H) Then
strCND = strCND & "((Conditions.Value) <= " &
Me.fCnd2H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits2) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits2 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd3) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd3
& """) And "
If Not IsNull(Me.fCnd3L) Then
strCND = strCND & "((Conditions.Value) >= " &
Me.fCnd3L
& ") And "
End If
If Not IsNull(Me.fCnd3H) Then
strCND = strCND & "((Conditions.Value) <= " &
Me.fCnd3H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits3) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits3 & """) And "
End If
End If

lenCND = Len(strCND) - 5
strCND = Left(strCND, lenCND)
'Debug.Print strCND
If segCND = 1 Then
strCND = strCND & ")"
End If
strSQL = "SELECT MLOBOOK.Description, Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM Conditions INNER JOIN ((MLOBOOK INNER JOIN Data ON
(MLOBOOK.CatalogID = Data.CatalogID) AND (MLOBOOK.CatalogYear " & _
"= Data.CatalogYear) AND (MLOBOOK.CatalogPrefix =
Data.CatalogPrefix)) INNER JOIN ConditionsDataJunction " & _
"ON Data.ID = ConditionsDataJunction.DataID) ON
Conditions.ID =
ConditionsDataJunction.ConditionsID " & _
"WHERE (((Data.CatalogYear) Is Not Null) AND (" & strCND &
"));"
'Debug.Print strSQL
Me.RecordSource = strSQL
End If

Dim strPrefix As String
Dim strYear As String
Dim strID As String

If Not IsNull(Me.cboMLOL) And IsNull(Me.cboMLOH) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And Not IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) and "
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) and "
End If
'Debug.Print strWhere

'Loop through the ItemsSelected in the list box.
Dim strProperty As String, PropertyLen As Long, varItem As
Variant,
strQ As String
strQ = """"
With Me.fProperty
For Each varItem In .ItemsSelected
'Build up the filter from the bound column (hidden).
strProperty = strProperty & strQ & .Column(varItem)
&
strQ & ", "
Next varItem
End With
Debug.Print strProperty

PropertyLen = Len(strProperty) - 2
Debug.Print PropertyLen

If PropertyLen > 0 Then
strProperty = "([Property] IN (" & Left$(strProperty,
PropertyLen) & "))"
strWhere = strWhere & strProperty & " AND "
End If
'Remove trailing comma. Add field name, IN operator, and
brackets.

'Loop through the ItemsSelected in the list box.
Dim strMethod As String, methodlen As Long
With Me.fMethod
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strMethod = strMethod & strQ & .ItemData(varItem) & strQ
&
","
End If
Next
End With

methodlen = Len(strMethod) - 1
If methodlen > 0 Then
strMethod = "[TestMethod] IN (" & Left$(strMethod,
methodlen) &
")"
End If
If Len(strWhere) > 0 And methodlen > 0 Then
strWhere = strWhere & "(" & strMethod & ") AND "
ElseIf Len(strWhere) <= 0 And methodlen > 0 Then
strWhere = strMethod & " AND "
Else
strWhere = strWhere
End If

'Remove trailing comma. Add field name, IN operator, and
brackets.

Click to expand...

Click to expand...

bymarce · Sep 18, 2009

There's no error. It just doesn't do anything when I click the button.
Marcie

Douglas J. Steele said:
What do you mean by "the record source won't change"? Are you getting an
error? If so, what's the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

bymarce said:

Hi again. I thought I had this worked out but when I put the code for
builidng a string from the list box before the code that changes the
record
source, the record source won't change. Why are these coflicting?
Marcie

Douglas J. Steele said:

Sorry, I only had time for a really quick look at the moment (hopefully
I'll
get some more time this evening), but in the code abourt fProperty,
you're
using

strProperty = strProperty & strQ & .Column(varItem) & strQ & ", "

That should be either

strProperty = strProperty & strQ & .ItemData(varItem) & strQ & ", "

(if you're trying to get the value of the bound column)

or

strProperty = strProperty & strQ & .Column(n, varItem) & strQ & ", "

(where n is the number of the column, 0-based, whose value you're trying
to
get)

However, it sounds as though it's not doing anything with your code if
strProperty isn't gettting any values in it. Before the With Me.fProperty
statement, try putting

MsgBox "There are " & Me!fProperty.ItemsSelected.Count & " items selected
in
listbox fProperty."

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here's the entire code for this button. I have about 20 unbound
controls
(combo, list, and text boxes) in the header of the form for filtering
experimental data based on 3 tables: MLOBOOK (sample catalog), DATA,
and
Conditions. I based this on Allen's filter form example and filtering
based
on a field in a subform. When I select only values in fProperty my
form
pops
up a message that says "No Criteria". Also debug.print PropertyLen
returns -2
in the immediate window. I'm also trying to have this button add to the
filter string using "OR" if the form is already filtered rather than
starting
from scratch every time. Thanks for your help.
Marcie

Private Sub cmdFilterAnd_Click() 'Adapted from
http://allenbrowne.com/ser-62code.html

Dim segCND As Long
Dim segNum As Long
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
segNum = 0
Dim strCND As String
Dim lenCND As Long
If Me.FilterOn = True Then
strWhere = Me.Filter
strWhere = "(" & strWhere & ") OR ("
segNum = 1
'Debug.Print strWhere
End If

strCND = Me.RecordSource
lenCND = Len(strCND)
'Debug.Print lenCND
If lenCND > 654 Then
strCND = Right(strCND, (lenCND - 654))
strCND = Left(strCND, Len(strCND) - 3)
'Debug.Print strCND
strCND = "(" & strCND & ") OR ("
segCND = 1
Else
strCND = ""
segCND = 0
End If

Dim strSQL As String
If IsNull(Me.fcboCnd1) And segCND = 0 Then
' If the combo is Null, use the whole table as the
RecordSource.
Me.RecordSource = "SELECT MLOBOOK.Description,
Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM MLOBOOK INNER JOIN Data ON (MLOBOOK.CatalogID =
Data.CatalogID) AND (MLOBOOK.CatalogYear = Data.CatalogYear) " & _
"AND (MLOBOOK.CatalogPrefix = Data.CatalogPrefix) WHERE
(((Data.CatalogYear) Is Not Null));"
Else
If Not IsNull(Me.fcboCnd1) Then
strCND = strCND & "((Conditions.ConditionName) Like """
&
Me.fcboCnd1 & """) And "
If Not IsNull(Me.fCnd1L) Then
strCND = strCND & "((Conditions.Value) >= " &
Me.fCnd1L
& ") And "
End If
If Not IsNull(Me.fCnd1H) Then
strCND = strCND & "((Conditions.Value) <= " &
Me.fCnd1H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits1) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits1 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd2) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd2
& """) And "
If Not IsNull(Me.fCnd2L) Then
strCND = strCND & "((Conditions.Value) >= " &
Me.fCnd2L
& ") And "
End If
If Not IsNull(Me.fCnd2H) Then
strCND = strCND & "((Conditions.Value) <= " &
Me.fCnd2H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits2) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits2 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd3) Then
strCND = "((Conditions.ConditionName) Like """ &
Me.fcboCnd3
& """) And "
If Not IsNull(Me.fCnd3L) Then
strCND = strCND & "((Conditions.Value) >= " &
Me.fCnd3L
& ") And "
End If
If Not IsNull(Me.fCnd3H) Then
strCND = strCND & "((Conditions.Value) <= " &
Me.fCnd3H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits3) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits3 & """) And "
End If
End If

lenCND = Len(strCND) - 5
strCND = Left(strCND, lenCND)
'Debug.Print strCND
If segCND = 1 Then
strCND = strCND & ")"
End If
strSQL = "SELECT MLOBOOK.Description, Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod,
Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM Conditions INNER JOIN ((MLOBOOK INNER JOIN Data ON
(MLOBOOK.CatalogID = Data.CatalogID) AND (MLOBOOK.CatalogYear " & _
"= Data.CatalogYear) AND (MLOBOOK.CatalogPrefix =
Data.CatalogPrefix)) INNER JOIN ConditionsDataJunction " & _
"ON Data.ID = ConditionsDataJunction.DataID) ON
Conditions.ID =
ConditionsDataJunction.ConditionsID " & _
"WHERE (((Data.CatalogYear) Is Not Null) AND (" & strCND &
"));"
'Debug.Print strSQL
Me.RecordSource = strSQL
End If

Dim strPrefix As String
Dim strYear As String
Dim strID As String

If Not IsNull(Me.cboMLOL) And IsNull(Me.cboMLOH) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) AND "
End If

If Not IsNull(Me.cboMLOH) And Not IsNull(Me.cboMLOL) Then
strPrefix = Me.cboMLOL
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOL
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOL
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] >= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] >= " & strID & ")) and "
strPrefix = Me.cboMLOH
strPrefix = Left(strPrefix, 3)
strYear = Me.cboMLOH
strYear = Right(strYear, 9)
strYear = Left(strYear, 4)
strID = Me.cboMLOH
strID = Val(Right(strID, 4))
'Debug.Print strPrefix
'Debug.Print strYear
'Debug.Print strID
strWhere = strWhere & "(([CatalogPrefix] Like """ & strPrefix &
""")
and "
strWhere = strWhere & "([CatalogYear] <= " & strYear & ") and "
strWhere = strWhere & "([CatalogID] <= " & strID & ")) and "
End If
'Debug.Print strWhere

'Loop through the ItemsSelected in the list box.
Dim strProperty As String, PropertyLen As Long, varItem As
Variant,
strQ As String
strQ = """"
With Me.fProperty
For Each varItem In .ItemsSelected
'Build up the filter from the bound column (hidden).
strProperty = strProperty & strQ & .Column(varItem)
&
strQ & ", "
Next varItem
End With
Debug.Print strProperty

PropertyLen = Len(strProperty) - 2
Debug.Print PropertyLen

Click to expand...

Click to expand...

Douglas J. Steele · Sep 18, 2009

What's your current code?

bymarce · Sep 24, 2009

Thanks for your help. I've got this more basic version of it working now. It
seems that I needed to build the filter string before setting the
recordsource and apply the filter string after setting the recordsource. Now
I'm going to try to add back the rest of the functinallity I had working
before. Thanks again.
Marcie

Private Sub cmdFilter_Click() 'Adapted from
http://allenbrowne.com/ser-62code.html
'Check if filter is on and what record source is being used.

Dim strWhere As String, lngLen As Long, strProperty As String,
PropertyLen As Long
Dim varItem As Variant, strQ As String
strQ = """"

With Me.fProperty
For Each varItem In .ItemsSelected
'Build filter string from selected items in listbox.
strProperty = strProperty & strQ & .ItemData(varItem) &
strQ & ", "
Next varItem
End With
Debug.Print strProperty

PropertyLen = Len(strProperty) - 2
Debug.Print PropertyLen

If PropertyLen > 0 Then
strProperty = "([Property] IN (" & Left$(strProperty,
PropertyLen) & "))"
strWhere = strWhere & strProperty & " AND "
End If
Debug.Print strWhere

Dim strCND As String, strSQL As String, lenCND As Long
If IsNull(Me.fcboCnd1) Then
'If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "SELECT MLOBOOK.Description, Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod, Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM MLOBOOK INNER JOIN Data ON (MLOBOOK.CatalogID =
Data.CatalogID) AND (MLOBOOK.CatalogYear = Data.CatalogYear) " & _
"AND (MLOBOOK.CatalogPrefix = Data.CatalogPrefix) WHERE
(((Data.CatalogYear) Is Not Null));"
Else
If Not IsNull(Me.fcboCnd1) Then
strCND = strCND & "((Conditions.ConditionName) Like """ &
Me.fcboCnd1 & """) And "
If Not IsNull(Me.fCnd1L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd1L
& ") And "
End If
If Not IsNull(Me.fCnd1H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd1H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits1) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits1 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd2) Then
strCND = "((Conditions.ConditionName) Like """ & Me.fcboCnd2
& """) And "
If Not IsNull(Me.fCnd2L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd2L
& ") And "
End If
If Not IsNull(Me.fCnd2H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd2H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits2) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits2 & """) And "
End If
End If
If Not IsNull(Me.fcboCnd3) Then
strCND = "((Conditions.ConditionName) Like """ & Me.fcboCnd3
& """) And "
If Not IsNull(Me.fCnd3L) Then
strCND = strCND & "((Conditions.Value) >= " & Me.fCnd3L
& ") And "
End If
If Not IsNull(Me.fCnd3H) Then
strCND = strCND & "((Conditions.Value) <= " & Me.fCnd3H
& ") And "
End If
If Not IsNull(Me.fcboCndUnits3) Then
strCND = strCND & "((Conditions.Units) Like """ &
Me.fcboCndUnits3 & """) And "
End If
End If

lenCND = Len(strCND) - 5
strCND = Left(strCND, lenCND)
Debug.Print strCND
strSQL = "SELECT MLOBOOK.Description, Data.CatalogYear,
Data.CatalogPrefix, Data.CatalogID, " & _
"Data.DateTested, Data.Property, Data.TestMethod, Data.RNumeric,
Data.RText, Data.Units, Data.Comments, " & _
"Data.TestRunBy, Data.TestAssignedTo, Data.Publication,
Data.TestAssignedDate, Data.ApprovedBy, Data.ID " & _
"FROM Conditions INNER JOIN ((MLOBOOK INNER JOIN Data ON
(MLOBOOK.CatalogID = Data.CatalogID) AND (MLOBOOK.CatalogYear " & _
"= Data.CatalogYear) AND (MLOBOOK.CatalogPrefix =
Data.CatalogPrefix)) INNER JOIN ConditionsDataJunction " & _
"ON Data.ID = ConditionsDataJunction.DataID) ON Conditions.ID =
ConditionsDataJunction.ConditionsID " & _
"WHERE (((Data.CatalogYear) Is Not Null) AND (" & strCND & "));"
Debug.Print strSQL
Me.RecordSource = strSQL
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 And IsNull(Me.fcboCnd1) Then
MsgBox "No Criteria", vbInformation, "Nothing to do."
ElseIf lngLen > 0 Then
strWhere = Left(strWhere, lngLen)
Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
Me.FilterString.ForeColor = vbBlack
End If

On Error GoTo Err_cmdFilterAnd_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70

Exit_cmdFilterAnd_Click:
Exit Sub

Err_cmdFilterAnd_Click:
MsgBox Err.Description
Resume Exit_cmdFilterAnd_Click

End Sub

Access Not Responding when VBA Code Run	3	Nov 3, 2009
Not Responding when VBA code is Run	2	Nov 3, 2009
Multiselect Listbox	3	Jan 29, 2009
Create Report On the Fly	1	Dec 18, 2009
HOW TO Simplify following code	4	May 13, 2010
Simplify Code-Delete Query	3	Jan 16, 2010
Two multi-select list boxes	3	Apr 28, 2009
Two list boxes on a search form	0	Mar 3, 2008

multiselect list box code for filter form

bymarce

Douglas J. Steele

bymarce

Douglas J. Steele

bymarce

bymarce

bymarce

bymarce

Douglas J. Steele

bymarce

Douglas J. Steele

bymarce

Ask a Question

Similar Threads