MultiList Select Box Search Form

sse1979 · Jan 26, 2010

I posted this question awhile ago, and never got a chance to get back to
check for any responses till now. I see Doug's (Thank you) and did what he
suggested, but got a kink while doing that. I also am not sure of if I
should post again, like I am now, or just reply to the old post. So
anyway.....
I came up with a problem when typing in
& _ (That is simply an ampersand and a underscore, correct?)
""", "
After I enter that code, those two lines turn red and I get a vb error
window saying: Compile error: Expected: line number or label or statement or
end of statement.
I also entered the declarations Doug mentioned.
When I try new code suggested, I get this error. Any ideas? Please?
I pasted the code below again. And I am also including the code for the
reset button, because from reading other posts, I think I'll probably have a
problem resetting the multiselect list box. So I thought maybe someone could
answer that too, if I have it wrong.
Thank you, Thank you, Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and"
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we do
nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Douglas J. Steele 12/5/2009 5:30 AM PST

Once you make a list box Multiselect, any reference to it will always
return
Null, whether or not anything's selected.

Replace

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If

with

'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "
End If

Make sure you add the following declarations to the code:

Dim strCounty As String
Dim varSelected As Variant

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to modify Allen Browne's Search form to use a multiselect list
box, but my listbox has only one column. I want to filter my list by
choosing multiple counties in my listbox (I also have other choices in text
boxes of state, date fields, etc.). I have pasted my code below for my
filter button. I'm not great with writing and deciphering code, but seem to
do ok sometimes in modifying, but I can't get this one. I've read through
many other posts here, and still can't figure it out.
Everything works till I put in the listbox. txtFilterCity is the name of my
lb. County is the name of the field I am querying. I also couldn't get the
reset button to clear the listbox, so help with that would be appreciated
too.
Thank you in advance.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

sse1979 said:
I'm trying to modify Allen Browne's Search form to use a multiselect list
box, but my listbox has only one column. I want to filter my list by
choosing multiple counties in my listbox (I also have other choices in
text
boxes of state, date fields, etc.). I have pasted my code below for my
filter button. I'm not great with writing and deciphering code, but seem
to
do ok sometimes in modifying, but I can't get this one. I've read through
many other posts here, and still can't figure it out.
Everything works till I put in the listbox. txtFilterCity is the name of
my
lb. County is the name of the field I am querying. I also couldn't get
the
reset button to clear the listbox, so help with that would be appreciated
too.
Thank you in advance.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards;
_
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """)
and "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL",
we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Daryl S · Jan 26, 2010

sse1979 -

The & _ problem is because you need to complete any text strings before the
& _. You have a leading double-quote in your text string, but want to put
the ending double-quote on the line after the & _ continuation. Keep that on
one line, like this:

strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected) & """,
"

--
Daryl S

sse1979 said:
I posted this question awhile ago, and never got a chance to get back to
check for any responses till now. I see Doug's (Thank you) and did what he
suggested, but got a kink while doing that. I also am not sure of if I
should post again, like I am now, or just reply to the old post. So
anyway.....
I came up with a problem when typing in
& _ (That is simply an ampersand and a underscore, correct?)
""", "
After I enter that code, those two lines turn red and I get a vb error
window saying: Compile error: Expected: line number or label or statement or
end of statement.
I also entered the declarations Doug mentioned.
When I try new code suggested, I get this error. Any ideas? Please?
I pasted the code below again. And I am also including the code for the
reset button, because from reading other posts, I think I'll probably have a
problem resetting the multiselect list box. So I thought maybe someone could
answer that too, if I have it wrong.
Thank you, Thank you, Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and"
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we do
nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Douglas J. Steele 12/5/2009 5:30 AM PST

Once you make a list box Multiselect, any reference to it will always
return
Null, whether or not anything's selected.

Replace

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If

with

'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "
End If

Make sure you add the following declarations to the code:

Dim strCounty As String
Dim varSelected As Variant

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to modify Allen Browne's Search form to use a multiselect list
box, but my listbox has only one column. I want to filter my list by
choosing multiple counties in my listbox (I also have other choices in text
boxes of state, date fields, etc.). I have pasted my code below for my
filter button. I'm not great with writing and deciphering code, but seem to
do ok sometimes in modifying, but I can't get this one. I've read through
many other posts here, and still can't figure it out.
Everything works till I put in the listbox. txtFilterCity is the name of my
lb. County is the name of the field I am querying. I also couldn't get the
reset button to clear the listbox, so help with that would be appreciated
too.
Thank you in advance.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

sse1979 said:

I'm trying to modify Allen Browne's Search form to use a multiselect list
box, but my listbox has only one column. I want to filter my list by
choosing multiple counties in my listbox (I also have other choices in
text
boxes of state, date fields, etc.). I have pasted my code below for my
filter button. I'm not great with writing and deciphering code, but seem
to
do ok sometimes in modifying, but I can't get this one. I've read through
many other posts here, and still can't figure it out.
Everything works till I put in the listbox. txtFilterCity is the name of
my
lb. County is the name of the field I am querying. I also couldn't get
the
reset button to clear the listbox, so help with that would be appreciated
too.
Thank you in advance.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards;

Click to expand...

sse1979 · Feb 1, 2010

thank you Daryl S.
I fixed what you said with the trailing & ""","
Now when I try to run it, I get an error: Runtime error 3075: Missing ), ],
or Item in query expression '([County] IN(" Rock",)'. When I click debug
here, the line that is yellow is the:
Me.Filter=strWhere
at the near bottom of all the code.
I made the listbox an Extended multiselect listbox.
Anyone have any advice? I posted the code for the Run Query button below.
Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected) &
""","
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "

End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

Daryl S said:
sse1979 -

The & _ problem is because you need to complete any text strings before the
& _. You have a leading double-quote in your text string, but want to put
the ending double-quote on the line after the & _ continuation. Keep that on
one line, like this:

strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected) & """,
"

--
Daryl S

sse1979 said:

I posted this question awhile ago, and never got a chance to get back to
check for any responses till now. I see Doug's (Thank you) and did what he
suggested, but got a kink while doing that. I also am not sure of if I
should post again, like I am now, or just reply to the old post. So
anyway.....
I came up with a problem when typing in
& _ (That is simply an ampersand and a underscore, correct?)
""", "
After I enter that code, those two lines turn red and I get a vb error
window saying: Compile error: Expected: line number or label or statement or
end of statement.
I also entered the declarations Doug mentioned.
When I try new code suggested, I get this error. Any ideas? Please?
I pasted the code below again. And I am also including the code for the
reset button, because from reading other posts, I think I'll probably have a
problem resetting the multiselect list box. So I thought maybe someone could
answer that too, if I have it wrong.
Thank you, Thank you, Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and"
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we do
nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Douglas J. Steele 12/5/2009 5:30 AM PST

Once you make a list box Multiselect, any reference to it will always
return
Null, whether or not anything's selected.

Replace

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If

with

'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "
End If

Make sure you add the following declarations to the code:

Dim strCounty As String
Dim varSelected As Variant

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to modify Allen Browne's Search form to use a multiselect list
box, but my listbox has only one column. I want to filter my list by
choosing multiple counties in my listbox (I also have other choices in text
boxes of state, date fields, etc.). I have pasted my code below for my
filter button. I'm not great with writing and deciphering code, but seem to
do ok sometimes in modifying, but I can't get this one. I've read through
many other posts here, and still can't figure it out.
Everything works till I put in the listbox. txtFilterCity is the name of my
lb. County is the name of the field I am querying. I also couldn't get the
reset button to clear the listbox, so help with that would be appreciated
too.
Thank you in advance.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

sse1979 said:

I'm trying to modify Allen Browne's Search form to use a multiselect list
box, but my listbox has only one column. I want to filter my list by
choosing multiple counties in my listbox (I also have other choices in
text
boxes of state, date fields, etc.). I have pasted my code below for my
filter button. I'm not great with writing and deciphering code, but seem
to
do ok sometimes in modifying, but I can't get this one. I've read through
many other posts here, and still can't figure it out.
Everything works till I put in the listbox. txtFilterCity is the name of
my
lb. County is the name of the field I am querying. I also couldn't get
the
reset button to clear the listbox, so help with that would be appreciated

Click to expand...

Click to expand...

Douglas J. Steele · Feb 2, 2010

Looks like a slight omission in the code.

Change

If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & _
Me.txtFilterCity.ItemData(varSelected) & ""","
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "
End If

to

If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & _
Me.txtFilterCity.ItemData(varSelected) & ""","
Next varSelected
strWhere = strWhere & "([County] IN (" & _
Left(strCounty, Len(strCounty) - 1) & ") and "
End If

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

sse1979 said:
thank you Daryl S.
I fixed what you said with the trailing & ""","
Now when I try to run it, I get an error: Runtime error 3075:
Missing ), ],
or Item in query expression '([County] IN(" Rock",)'. When I click debug
here, the line that is yellow is the:
Me.Filter=strWhere
at the near bottom of all the code.
I made the listbox an Extended multiselect listbox.
Anyone have any advice? I posted the code for the Run Query button below.
Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards;
_
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected) &
""","
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "

End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL",
we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

Daryl S said:

sse1979 -

The & _ problem is because you need to complete any text strings before
the
& _. You have a leading double-quote in your text string, but want to
put
the ending double-quote on the line after the & _ continuation. Keep
that on
one line, like this:

strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected) &
""",
"

--
Daryl S

sse1979 said:

I posted this question awhile ago, and never got a chance to get back
to
check for any responses till now. I see Doug's (Thank you) and did
what he
suggested, but got a kink while doing that. I also am not sure of if I
should post again, like I am now, or just reply to the old post. So
anyway.....
I came up with a problem when typing in
& _ (That is simply an ampersand and a underscore, correct?)
""", "
After I enter that code, those two lines turn red and I get a vb error
window saying: Compile error: Expected: line number or label or
statement or
end of statement.
I also entered the declarations Doug mentioned.
When I try new code suggested, I get this error. Any ideas? Please?
I pasted the code below again. And I am also including the code for
the
reset button, because from reading other posts, I think I'll probably
have a
problem resetting the multiselect list box. So I thought maybe someone
could
answer that too, if I have it wrong.
Thank you, Thank you, Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and"
End If

'Yes/No field and combo example. If combo is blank or contains "ALL",
we do
nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters
and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate
+ 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show
all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Douglas J. Steele 12/5/2009 5:30 AM PST

Once you make a list box Multiselect, any reference to it will always
return
Null, whether or not anything's selected.

Replace

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If

with

'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "
End If

Make sure you add the following declarations to the code:

Dim strCounty As String
Dim varSelected As Variant

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to modify Allen Browne's Search form to use a multiselect
list
box, but my listbox has only one column. I want to filter my list by
choosing multiple counties in my listbox (I also have other choices in
text
boxes of state, date fields, etc.). I have pasted my code below for my
filter button. I'm not great with writing and deciphering code, but
seem to
do ok sometimes in modifying, but I can't get this one. I've read
through
many other posts here, and still can't figure it out.
Everything works till I put in the listbox. txtFilterCity is the name
of my
lb. County is the name of the field I am querying. I also couldn't get
the
reset button to clear the listbox, so help with that would be
appreciated
too.
Thank you in advance.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([County] = """ & Me.txtFilterCity & """) and "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL",
we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

I'm trying to modify Allen Browne's Search form to use a multiselect
list
box, but my listbox has only one column. I want to filter my list by
choosing multiple counties in my listbox (I also have other choices
in
text
boxes of state, date fields, etc.). I have pasted my code below for
my
filter button. I'm not great with writing and deciphering code, but
seem
to
do ok sometimes in modifying, but I can't get this one. I've read
through
many other posts here, and still can't figure it out.
Everything works till I put in the listbox. txtFilterCity is the name
of
my
lb. County is the name of the field I am querying. I also couldn't
get
the
reset button to clear the listbox, so help with that would be
appreciated

Click to expand...

Click to expand...

Daryl S · Feb 2, 2010

Thanks Doug. Yes, you can't have a trailing comma in the IN clause IN("
Rock",) should be IN ("Rock"). The leading space should not be there
unless it is really in the data (I hope not!).

--
Daryl S

Douglas J. Steele said:
Looks like a slight omission in the code.

Change

If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & _
Me.txtFilterCity.ItemData(varSelected) & ""","
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "
End If

to

If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & _
Me.txtFilterCity.ItemData(varSelected) & ""","
Next varSelected
strWhere = strWhere & "([County] IN (" & _
Left(strCounty, Len(strCounty) - 1) & ") and "
End If

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

sse1979 said:

thank you Daryl S.
I fixed what you said with the trailing & ""","
Now when I try to run it, I get an error: Runtime error 3075:
Missing ), ],
or Item in query expression '([County] IN(" Rock",)'. When I click debug
here, the line that is yellow is the:
Me.Filter=strWhere
at the near bottom of all the code.
I made the listbox an Extended multiselect listbox.
Anyone have any advice? I posted the code for the Run Query button below.
Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards;
_
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected) &
""","
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "

End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL",
we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

Daryl S said:

sse1979 -

The & _ problem is because you need to complete any text strings before
the
& _. You have a leading double-quote in your text string, but want to
put
the ending double-quote on the line after the & _ continuation. Keep
that on
one line, like this:

strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected) &
""",
"

--
Daryl S

:

I posted this question awhile ago, and never got a chance to get back
to
check for any responses till now. I see Doug's (Thank you) and did
what he
suggested, but got a kink while doing that. I also am not sure of if I
should post again, like I am now, or just reply to the old post. So
anyway.....
I came up with a problem when typing in
& _ (That is simply an ampersand and a underscore, correct?)
""", "
After I enter that code, those two lines turn red and I get a vb error
window saying: Compile error: Expected: line number or label or
statement or
end of statement.
I also entered the declarations Doug mentioned.
When I try new code suggested, I get this error. Any ideas? Please?
I pasted the code below again. And I am also including the code for
the
reset button, because from reading other posts, I think I'll probably
have a
problem resetting the multiselect list box. So I thought maybe someone
could
answer that too, if I have it wrong.
Thank you, Thank you, Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and"
End If

'Yes/No field and combo example. If combo is blank or contains "ALL",
we do
nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters
and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate
+ 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show
all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox

Click to expand...

Click to expand...

sse1979 · Feb 2, 2010

Thank you. That leading space was in my data, unfortunately (I've since
removed it)! But even making the change/addition to the code Doug
recommended here, I am still getting the same error of: Runtime error 3075:
Missing ), ], or Item in query expression '([County] IN ("Rock")'. When I
click debug on this error window, Me.Filter=strWhere is yellow and ([County]
IN ("Adams") shows up in the immediate window as the bug.
HELP!!!!

Daryl S said:
Thanks Doug. Yes, you can't have a trailing comma in the IN clause IN("
Rock",) should be IN ("Rock"). The leading space should not be there
unless it is really in the data (I hope not!).

--
Daryl S

Douglas J. Steele said:

Looks like a slight omission in the code.

Change

If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & _
Me.txtFilterCity.ItemData(varSelected) & ""","
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "
End If

to

If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & _
Me.txtFilterCity.ItemData(varSelected) & ""","
Next varSelected
strWhere = strWhere & "([County] IN (" & _
Left(strCounty, Len(strCounty) - 1) & ") and "
End If

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

sse1979 said:

thank you Daryl S.
I fixed what you said with the trailing & ""","
Now when I try to run it, I get an error: Runtime error 3075:
Missing ), ],
or Item in query expression '([County] IN(" Rock",)'. When I click debug
here, the line that is yellow is the:
Me.Filter=strWhere
at the near bottom of all the code.
I made the listbox an Extended multiselect listbox.
Anyone have any advice? I posted the code for the Run Query button below.
Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards;
_
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected) &
""","
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "

End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtFilterMainName &
"*"") AND "
End If

'Yes/No field and combo example. If combo is blank or contains "ALL",
we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

:

sse1979 -

The & _ problem is because you need to complete any text strings before
the
& _. You have a leading double-quote in your text string, but want to
put
the ending double-quote on the line after the & _ continuation. Keep
that on
one line, like this:

strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected) &
""",
"

--
Daryl S

:

I posted this question awhile ago, and never got a chance to get back
to
check for any responses till now. I see Doug's (Thank you) and did
what he
suggested, but got a kink while doing that. I also am not sure of if I
should post again, like I am now, or just reply to the old post. So
anyway.....
I came up with a problem when typing in
& _ (That is simply an ampersand and a underscore, correct?)
""", "
After I enter that code, those two lines turn red and I get a vb error
window saying: Compile error: Expected: line number or label or
statement or
end of statement.
I also entered the declarations Doug mentioned.
When I try new code suggested, I get this error. Any ideas? Please?
I pasted the code below again. And I am also including the code for
the
reset button, because from reading other posts, I think I'll probably
have a
problem resetting the multiselect list box. So I thought maybe someone
could
answer that too, if I have it wrong.
Thank you, Thank you, Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and"
End If

'Yes/No field and combo example. If combo is blank or contains "ALL",
we do
nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters
and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate
+ 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show

Click to expand...

Click to expand...

Douglas J. Steele · Feb 2, 2010

Change

strWhere = strWhere & "([County] IN (" & strCounty & ") and "

to

strWhere = strWhere & "([County] IN (" & strCounty & ")) and "

or

strWhere = strWhere & "[County] IN (" & strCounty & ") and "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

sse1979 said:
Thank you. That leading space was in my data, unfortunately (I've since
removed it)! But even making the change/addition to the code Doug
recommended here, I am still getting the same error of: Runtime error
3075:
Missing ), ], or Item in query expression '([County] IN ("Rock")'. When I
click debug on this error window, Me.Filter=strWhere is yellow and
([County]
IN ("Adams") shows up in the immediate window as the bug.
HELP!!!!

Daryl S said:

Thanks Doug. Yes, you can't have a trailing comma in the IN clause
IN("
Rock",) should be IN ("Rock"). The leading space should not be there
unless it is really in the data (I hope not!).

--
Daryl S

Douglas J. Steele said:

Looks like a slight omission in the code.

Change

If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & _
Me.txtFilterCity.ItemData(varSelected) & ""","
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "
End If

to

If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & _
Me.txtFilterCity.ItemData(varSelected) & ""","
Next varSelected
strWhere = strWhere & "([County] IN (" & _
Left(strCounty, Len(strCounty) - 1) & ") and "
End If

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

thank you Daryl S.
I fixed what you said with the trailing & ""","
Now when I try to run it, I get an error: Runtime error 3075:
Missing ), ],
or Item in query expression '([County] IN(" Rock",)'. When I click
debug
here, the line that is yellow is the:
Me.Filter=strWhere
at the near bottom of all the code.
I made the listbox an Extended multiselect listbox.
Anyone have any advice? I posted the code for the Run Query button
below.
Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards;
_
End date only = all dates up to (and
including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria
string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from
the
non-blank ones.

'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" &
Me.txtFilterCity.ItemData(varSelected) &
""","
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "

End If

'Another text field example. Use Like to find anywhere in the
field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" &
Me.txtFilterMainName &
"*"") AND "
End If

'Yes/No field and combo example. If combo is blank or contains
"ALL",
we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters
and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate
+ 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

:

sse1979 -

The & _ problem is because you need to complete any text strings
before
the
& _. You have a leading double-quote in your text string, but want
to
put
the ending double-quote on the line after the & _ continuation.
Keep
that on
one line, like this:

strCounty = strCounty & """" &
Me.txtFilterCity.ItemData(varSelected) &
""",
"

--
Daryl S

:

I posted this question awhile ago, and never got a chance to get
back
to
check for any responses till now. I see Doug's (Thank you) and
did
what he
suggested, but got a kink while doing that. I also am not sure of
if I
should post again, like I am now, or just reply to the old post.
So
anyway.....
I came up with a problem when typing in
& _ (That is simply an ampersand and a underscore, correct?)
""", "
After I enter that code, those two lines turn red and I get a vb
error
window saying: Compile error: Expected: line number or label or
statement or
end of statement.
I also entered the declarations Doug mentioned.
When I try new code suggested, I get this error. Any ideas?
Please?
I pasted the code below again. And I am also including the code
for
the
reset button, because from reading other posts, I think I'll
probably
have a
problem resetting the multiselect list box. So I thought maybe
someone
could
answer that too, if I have it wrong.
Thank you, Thank you, Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from
the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" &
Me.txtFilterCity.ItemData(varSelected)
& _
""", "
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and"
End If

'Yes/No field and combo example. If combo is blank or contains
"ALL",
we do
nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the #
delimiters
and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day"
since
this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " &
Format(Me.txtEndDate
+ 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the
form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND
")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so
remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and
show

Click to expand...

Click to expand...

sse1979 · Feb 3, 2010

Awesome, somehow with this post and your last post, I got it to work!!!
Thank you so much!

Douglas J. Steele said:
Change

strWhere = strWhere & "([County] IN (" & strCounty & ") and "

to

strWhere = strWhere & "([County] IN (" & strCounty & ")) and "

or

strWhere = strWhere & "[County] IN (" & strCounty & ") and "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

sse1979 said:

Thank you. That leading space was in my data, unfortunately (I've since
removed it)! But even making the change/addition to the code Doug
recommended here, I am still getting the same error of: Runtime error
3075:
Missing ), ], or Item in query expression '([County] IN ("Rock")'. When I
click debug on this error window, Me.Filter=strWhere is yellow and
([County]
IN ("Adams") shows up in the immediate window as the bug.
HELP!!!!

Daryl S said:

Thanks Doug. Yes, you can't have a trailing comma in the IN clause
IN("
Rock",) should be IN ("Rock"). The leading space should not be there
unless it is really in the data (I hope not!).

--
Daryl S

:

Looks like a slight omission in the code.

Change

If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & _
Me.txtFilterCity.ItemData(varSelected) & ""","
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "
End If

to

If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" & _
Me.txtFilterCity.ItemData(varSelected) & ""","
Next varSelected
strWhere = strWhere & "([County] IN (" & _
Left(strCounty, Len(strCounty) - 1) & ") and "
End If

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

thank you Daryl S.
I fixed what you said with the trailing & ""","
Now when I try to run it, I get an error: Runtime error 3075:
Missing ), ],
or Item in query expression '([County] IN(" Rock",)'. When I click
debug
here, the line that is yellow is the:
Me.Filter=strWhere
at the near bottom of all the code.
I made the listbox an Extended multiselect listbox.
Anyone have any advice? I posted the code for the Run Query button
below.
Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards;
_
End date only = all dates up to (and
including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria
string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from
the
non-blank ones.

'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected
strCounty = strCounty & """" &
Me.txtFilterCity.ItemData(varSelected) &
""","
Next varSelected
strWhere = strWhere & "([County] IN (" & strCounty & ") and "

End If

'Another text field example. Use Like to find anywhere in the
field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([State] Like ""*" &
Me.txtFilterMainName &
"*"") AND "
End If

'Yes/No field and combo example. If combo is blank or contains
"ALL",
we
do nothing.
If Me.cboFilterIsCorporate = -1 Then
strWhere = strWhere & "([WaterFrontage] = True) AND "
ElseIf Me.cboFilterIsCorporate = 0 Then
strWhere = strWhere & "([WaterFrontage] = False) AND "
End If

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

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

'Date field example. Use the format string to add the # delimiters
and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Sale Date] >= " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Sale Date] < " & Format(Me.txtEndDate
+ 1,
conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

:

sse1979 -

The & _ problem is because you need to complete any text strings
before
the
& _. You have a leading double-quote in your text string, but want
to
put
the ending double-quote on the line after the & _ continuation.
Keep
that on
one line, like this:

strCounty = strCounty & """" &
Me.txtFilterCity.ItemData(varSelected) &
""",
"

--
Daryl S

:

I posted this question awhile ago, and never got a chance to get
back
to
check for any responses till now. I see Doug's (Thank you) and
did
what he
suggested, but got a kink while doing that. I also am not sure of
if I
should post again, like I am now, or just reply to the old post.
So
anyway.....
I came up with a problem when typing in
& _ (That is simply an ampersand and a underscore, correct?)
""", "
After I enter that code, those two lines turn red and I get a vb
error
window saying: Compile error: Expected: line number or label or
statement or
end of statement.
I also entered the declarations Doug mentioned.
When I try new code suggested, I get this error. Any ideas?
Please?
I pasted the code below again. And I am also including the code
for
the
reset button, because from reading other posts, I think I'll
probably
have a
problem resetting the multiselect list box. So I thought maybe
someone
could
answer that too, if I have it wrong.
Thank you, Thank you, Thank you.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String
Dim strCounty As String 'The criteria string.
Dim lngLen As Long
Dim varSelected As Variant 'Length of the criteria string
to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates
in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from
the
non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Me.txtFilterCity.ItemsSelected.Count > 0 Then
For Each varSelected In Me.txtFilterCity.ItemsSelected

Click to expand...

Click to expand...

Multiselect List Box	4	Dec 4, 2009
Form filter- numbers rather than text	2	Feb 11, 2009
Sorting Allen Browne's Search Form	2	Jan 22, 2009
Search working in one form but not another	4	Apr 23, 2009
Type mismatch	2	Apr 9, 2009
Help with a Query running off of VBA	4	May 18, 2009
Update All Using a Command Button	3	Feb 27, 2008
How to Search Multiple Values in a Filter	5	Sep 15, 2008

MultiList Select Box Search Form

sse1979

Daryl S

sse1979

Douglas J. Steele

Daryl S

sse1979

Douglas J. Steele

sse1979

Ask a Question

Similar Threads