Use wildcard in combo box with multiple strings

[Guest]

I have a form with a combo box (BatchID) that when changed will populate the
detail in the form. I want to be able to use multiple wildcard strings to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the BatchId
field. This works fine in the query directly, but when I put the text in the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the BatchID
combo box.

 

[Allen Browne]

The query will not be able to interpret multiple criteria like that, so you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound multi-select
list box on your form, and build the string as shown in this article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report, but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub

 

[Guest]

I can not use the multi-select list box - there are just too many user
defined possibilities (although I printed the article for a different project
- thanks.)

I tried the code you provided for multiple keywords and I had two issues

1) the code... strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR " ...what does the [Notes] mean? For it to work I had to revise
the code to be... strWhere = strWhere & "Like ""*" & strWord & "*"" OR "
(Per the Locals window this is working fine)

2) When I try to run the code and get to the line "Me.Filter =
Left(strWhere, lngLen)" I get a run time error "run-time error '2448': You
can't assign a value to this object." I can not figure out what is
wrong...the Left function give the correct sting (I tested that separately)

PS I made sure the form is set to allow filters, just in case that was the
problem.

The query will not be able to interpret multiple criteria like that, so you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound multi-select
list box on your form, and build the string as shown in this article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report, but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a combo box (BatchID) that when changed will populate
the
detail in the form. I want to be able to use multiple wildcard strings to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the BatchId
field. This works fine in the query directly, but when I put the text in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the BatchID
combo box.

 

[Guest]

I've just been wrestling with same error. I got the error when there was a
problem with the query string I was trying to assign to the Filter property.
Try double-checking the value you're assigning.

I can not use the multi-select list box - there are just too many user
defined possibilities (although I printed the article for a different project
- thanks.)

I tried the code you provided for multiple keywords and I had two issues

1) the code... strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR " ...what does the [Notes] mean? For it to work I had to revise
the code to be... strWhere = strWhere & "Like ""*" & strWord & "*"" OR "
(Per the Locals window this is working fine)

2) When I try to run the code and get to the line "Me.Filter =
Left(strWhere, lngLen)" I get a run time error "run-time error '2448': You
can't assign a value to this object." I can not figure out what is
wrong...the Left function give the correct sting (I tested that separately)

PS I made sure the form is set to allow filters, just in case that was the
problem.

The query will not be able to interpret multiple criteria like that, so you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound multi-select
list box on your form, and build the string as shown in this article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report, but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a combo box (BatchID) that when changed will populate
the
detail in the form. I want to be able to use multiple wildcard strings to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the BatchId
field. This works fine in the query directly, but when I put the text in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the BatchID
combo box.

 

[Guest]

the string looks good to me. For four values in box
0506 0517 0515 0516
the string is
"(Like "*0506*") OR (Like "*0517*") OR (Like "*0515*") OR (Like "*0516*")"

I've just been wrestling with same error. I got the error when there was a
problem with the query string I was trying to assign to the Filter property.
Try double-checking the value you're assigning.

I can not use the multi-select list box - there are just too many user
defined possibilities (although I printed the article for a different project
- thanks.)

I tried the code you provided for multiple keywords and I had two issues

1) the code... strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR " ...what does the [Notes] mean? For it to work I had to revise
the code to be... strWhere = strWhere & "Like ""*" & strWord & "*"" OR "
(Per the Locals window this is working fine)

2) When I try to run the code and get to the line "Me.Filter =
Left(strWhere, lngLen)" I get a run time error "run-time error '2448': You
can't assign a value to this object." I can not figure out what is
wrong...the Left function give the correct sting (I tested that separately)

PS I made sure the form is set to allow filters, just in case that was the
problem.

The query will not be able to interpret multiple criteria like that, so you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound multi-select
list box on your form, and build the string as shown in this article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report, but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a combo box (BatchID) that when changed will populate
the
detail in the form. I want to be able to use multiple wildcard strings to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the BatchId
field. This works fine in the query directly, but when I put the text in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the BatchID
combo box.

 

[Douglas J Steele]

You must have the name of the field against which you're comparing: that's
what Allen meant by [Notes] in his original example. Replace [Notes] with
the appropriate field name.

The string you use as a filter must repeat the name of the field for each
comparison. That means you need something like:

Me.Filter = "([Notes] Like '*0506*') OR ([Notes] Like '*0517*') OR ([Notes]
Like '*0515*') OR ([Notes] Like '*0516*')"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I can not use the multi-select list box - there are just too many user
defined possibilities (although I printed the article for a different project
- thanks.)

I tried the code you provided for multiple keywords and I had two issues

1) the code... strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR " ...what does the [Notes] mean? For it to work I had to revise
the code to be... strWhere = strWhere & "Like ""*" & strWord & "*"" OR "
(Per the Locals window this is working fine)

2) When I try to run the code and get to the line "Me.Filter =
Left(strWhere, lngLen)" I get a run time error "run-time error '2448': You
can't assign a value to this object." I can not figure out what is
wrong...the Left function give the correct sting (I tested that separately)

PS I made sure the form is set to allow filters, just in case that was the
problem.

The query will not be able to interpret multiple criteria like that, so you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound multi-select
list box on your form, and build the string as shown in this article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report, but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a combo box (BatchID) that when changed will populate
the
detail in the form. I want to be able to use multiple wildcard strings to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the BatchId
field. This works fine in the query directly, but when I put the text in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the BatchID
combo box.

 

[Guest]

Perfect!!! I knew I must have been missing something with the [Notes]
reference. Thank you all!

You must have the name of the field against which you're comparing: that's
what Allen meant by [Notes] in his original example. Replace [Notes] with
the appropriate field name.

The string you use as a filter must repeat the name of the field for each
comparison. That means you need something like:

Me.Filter = "([Notes] Like '*0506*') OR ([Notes] Like '*0517*') OR ([Notes]
Like '*0515*') OR ([Notes] Like '*0516*')"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I can not use the multi-select list box - there are just too many user
defined possibilities (although I printed the article for a different project
- thanks.)

I tried the code you provided for multiple keywords and I had two issues

1) the code... strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR " ...what does the [Notes] mean? For it to work I had to revise
the code to be... strWhere = strWhere & "Like ""*" & strWord & "*"" OR "
(Per the Locals window this is working fine)

2) When I try to run the code and get to the line "Me.Filter =
Left(strWhere, lngLen)" I get a run time error "run-time error '2448': You
can't assign a value to this object." I can not figure out what is
wrong...the Left function give the correct sting (I tested that separately)

PS I made sure the form is set to allow filters, just in case that was the
problem.

The query will not be able to interpret multiple criteria like that, so you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound multi-select
list box on your form, and build the string as shown in this article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report, but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a combo box (BatchID) that when changed will populate
the
detail in the form. I want to be able to use multiple wildcard strings to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the BatchId
field. This works fine in the query directly, but when I put the text in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the BatchID
combo box.

 

[Guest]

New spin on the old question...

I have a report that works off this same text box. The report uses these
batch ids to LIMIT the records but it does not USE the batch id field in the
output so I can't filter by that field, right? The report takes these
multiple batch ids and cross references them to a materials list and then
groups and subtotals by the raw material item (not the batch id). What do I
do now? How do I use multiple strings in a combo box to limit records in a
report but not output that field in the report itself?

Perfect!!! I knew I must have been missing something with the [Notes]
reference. Thank you all!

You must have the name of the field against which you're comparing: that's
what Allen meant by [Notes] in his original example. Replace [Notes] with
the appropriate field name.

The string you use as a filter must repeat the name of the field for each
comparison. That means you need something like:

Me.Filter = "([Notes] Like '*0506*') OR ([Notes] Like '*0517*') OR ([Notes]
Like '*0515*') OR ([Notes] Like '*0516*')"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I can not use the multi-select list box - there are just too many user
defined possibilities (although I printed the article for a different project
- thanks.)

I tried the code you provided for multiple keywords and I had two issues

1) the code... strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR " ...what does the [Notes] mean? For it to work I had to revise
the code to be... strWhere = strWhere & "Like ""*" & strWord & "*"" OR "
(Per the Locals window this is working fine)

2) When I try to run the code and get to the line "Me.Filter =
Left(strWhere, lngLen)" I get a run time error "run-time error '2448': You
can't assign a value to this object." I can not figure out what is
wrong...the Left function give the correct sting (I tested that separately)

PS I made sure the form is set to allow filters, just in case that was the
problem.


:

The query will not be able to interpret multiple criteria like that, so you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound multi-select
list box on your form, and build the string as shown in this article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report, but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a combo box (BatchID) that when changed will populate
the
detail in the form. I want to be able to use multiple wildcard strings to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the BatchId
field. This works fine in the query directly, but when I put the text in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the BatchID
combo box.

 

[Douglas J. Steele]

Just because the value doesn't appear on the report doesn't mean you can't
use the field to filter what rows are in the RecordSource for the report.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

New spin on the old question...

I have a report that works off this same text box. The report uses these
batch ids to LIMIT the records but it does not USE the batch id field in
the
output so I can't filter by that field, right? The report takes these
multiple batch ids and cross references them to a materials list and then
groups and subtotals by the raw material item (not the batch id). What do
I
do now? How do I use multiple strings in a combo box to limit records in
a
report but not output that field in the report itself?

Perfect!!! I knew I must have been missing something with the [Notes]
reference. Thank you all!

You must have the name of the field against which you're comparing:
that's
what Allen meant by [Notes] in his original example. Replace [Notes]
with
the appropriate field name.

The string you use as a filter must repeat the name of the field for
each
comparison. That means you need something like:

Me.Filter = "([Notes] Like '*0506*') OR ([Notes] Like '*0517*') OR
([Notes]
Like '*0515*') OR ([Notes] Like '*0516*')"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I can not use the multi-select list box - there are just too many
user
defined possibilities (although I printed the article for a different
project
- thanks.)

I tried the code you provided for multiple keywords and I had two
issues

1) the code... strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR " ...what does the [Notes] mean? For it to work I had to
revise
the code to be... strWhere = strWhere & "Like ""*" & strWord & "*""
OR "
(Per the Locals window this is working fine)

2) When I try to run the code and get to the line "Me.Filter =
Left(strWhere, lngLen)" I get a run time error "run-time error
'2448': You
can't assign a value to this object." I can not figure out what is
wrong...the Left function give the correct sting (I tested that
separately)

PS I made sure the form is set to allow filters, just in case that
was the
problem.


:

The query will not be able to interpret multiple criteria like
that, so
you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound
multi-select
list box on your form, and build the string as shown in this
article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report,
but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter
string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" &
strWord
&
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I have a form with a combo box (BatchID) that when changed will
populate
the
detail in the form. I want to be able to use multiple wildcard
strings to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the
BatchId
field. This works fine in the query directly, but when I put the
text
in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the
BatchID
combo box.

 

[Guest]

I have a summary report by BatchId and it is filtering fine. The detail
report that does not have BatchId in the output is giving me an error -
"Enter Parameter Value tblBatchPickList.BatchID" (tblBatchPickList.BatchID
is the field I am trying to filter on.) I am assuming it is asking because
it can not find that field.

The report's data is based on a embedded query that has tblBatchPickList in
the table list, but not in the output of the query.

Just because the value doesn't appear on the report doesn't mean you can't
use the field to filter what rows are in the RecordSource for the report.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

New spin on the old question...

I have a report that works off this same text box. The report uses these
batch ids to LIMIT the records but it does not USE the batch id field in
the
output so I can't filter by that field, right? The report takes these
multiple batch ids and cross references them to a materials list and then
groups and subtotals by the raw material item (not the batch id). What do
I
do now? How do I use multiple strings in a combo box to limit records in
a
report but not output that field in the report itself?

Perfect!!! I knew I must have been missing something with the [Notes]
reference. Thank you all!

:

You must have the name of the field against which you're comparing:
that's
what Allen meant by [Notes] in his original example. Replace [Notes]
with
the appropriate field name.

The string you use as a filter must repeat the name of the field for
each
comparison. That means you need something like:

Me.Filter = "([Notes] Like '*0506*') OR ([Notes] Like '*0517*') OR
([Notes]
Like '*0515*') OR ([Notes] Like '*0516*')"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I can not use the multi-select list box - there are just too many
user
defined possibilities (although I printed the article for a different
project
- thanks.)

I tried the code you provided for multiple keywords and I had two
issues

1) the code... strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR " ...what does the [Notes] mean? For it to work I had to
revise
the code to be... strWhere = strWhere & "Like ""*" & strWord & "*""
OR "
(Per the Locals window this is working fine)

2) When I try to run the code and get to the line "Me.Filter =
Left(strWhere, lngLen)" I get a run time error "run-time error
'2448': You
can't assign a value to this object." I can not figure out what is
wrong...the Left function give the correct sting (I tested that
separately)

PS I made sure the form is set to allow filters, just in case that
was the
problem.


:

The query will not be able to interpret multiple criteria like
that, so
you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound
multi-select
list box on your form, and build the string as shown in this
article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report,
but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter
string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" &
strWord
&
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I have a form with a combo box (BatchID) that when changed will
populate
the
detail in the form. I want to be able to use multiple wildcard
strings to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the
BatchId
field. This works fine in the query directly, but when I put the
text
in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the
BatchID
combo box.

 

[Douglas J. Steele]

Add the field to the query then. Just because a field is in the query
doesn't mean you have to display it on the form. However, if you want to
filter by it, it has to be in the query...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a summary report by BatchId and it is filtering fine. The detail
report that does not have BatchId in the output is giving me an error -
"Enter Parameter Value tblBatchPickList.BatchID"
(tblBatchPickList.BatchID
is the field I am trying to filter on.) I am assuming it is asking
because
it can not find that field.

The report's data is based on a embedded query that has tblBatchPickList
in
the table list, but not in the output of the query.

Just because the value doesn't appear on the report doesn't mean you
can't
use the field to filter what rows are in the RecordSource for the report.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

New spin on the old question...

I have a report that works off this same text box. The report uses
these
batch ids to LIMIT the records but it does not USE the batch id field
in
the
output so I can't filter by that field, right? The report takes these
multiple batch ids and cross references them to a materials list and
then
groups and subtotals by the raw material item (not the batch id). What
do
I
do now? How do I use multiple strings in a combo box to limit records
in
a
report but not output that field in the report itself?


:

Perfect!!! I knew I must have been missing something with the [Notes]
reference. Thank you all!

:

You must have the name of the field against which you're comparing:
that's
what Allen meant by [Notes] in his original example. Replace [Notes]
with
the appropriate field name.

The string you use as a filter must repeat the name of the field for
each
comparison. That means you need something like:

Me.Filter = "([Notes] Like '*0506*') OR ([Notes] Like '*0517*') OR
([Notes]
Like '*0515*') OR ([Notes] Like '*0516*')"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I can not use the multi-select list box - there are just too many
user
defined possibilities (although I printed the article for a
different
project
- thanks.)

I tried the code you provided for multiple keywords and I had two
issues

1) the code... strWhere = strWhere & "([Notes] Like ""*" &
strWord &
"*"") OR " ...what does the [Notes] mean? For it to work I had
to
revise
the code to be... strWhere = strWhere & "Like ""*" & strWord &
"*""
OR "
(Per the Locals window this is working fine)

2) When I try to run the code and get to the line "Me.Filter =
Left(strWhere, lngLen)" I get a run time error "run-time error
'2448': You
can't assign a value to this object." I can not figure out what
is
wrong...the Left function give the correct sting (I tested that
separately)

PS I made sure the form is set to allow filters, just in case that
was the
problem.


:

The query will not be able to interpret multiple criteria like
that, so
you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound
multi-select
list box on your form, and build the string as shown in this
article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a
report,
but
building the Filter for a form is identical.

Another option would be just to type all the values into a text
box
separated by spaces, and use code like this to build the filter
string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" &
strWord
&
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR
".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I have a form with a combo box (BatchID) that when changed will
populate
the
detail in the form. I want to be able to use multiple
wildcard
strings to
search the database for the detail. For example, I want to
enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the
BatchId
field. This works fine in the query directly, but when I put
the
text
in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to
the
BatchID
combo box.

 

[Guest]

That is exactly my problem. I CAN'T add it to the query. The query is a
summary query and the filter will not work if I add the field as a where or
expression field and I CAN'T add it as a group or it will mess up the report
results.

My problem is I can not add the field to the query and I can not filter if
the field is not in the query.

My question is - How do I query for a report for multiple wildcards strings
in a text box when I can not have the text box field in the report's query?

Add the field to the query then. Just because a field is in the query
doesn't mean you have to display it on the form. However, if you want to
filter by it, it has to be in the query...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a summary report by BatchId and it is filtering fine. The detail
report that does not have BatchId in the output is giving me an error -
"Enter Parameter Value tblBatchPickList.BatchID"
(tblBatchPickList.BatchID
is the field I am trying to filter on.) I am assuming it is asking
because
it can not find that field.

The report's data is based on a embedded query that has tblBatchPickList
in
the table list, but not in the output of the query.

Just because the value doesn't appear on the report doesn't mean you
can't
use the field to filter what rows are in the RecordSource for the report.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


New spin on the old question...

I have a report that works off this same text box. The report uses
these
batch ids to LIMIT the records but it does not USE the batch id field
in
the
output so I can't filter by that field, right? The report takes these
multiple batch ids and cross references them to a materials list and
then
groups and subtotals by the raw material item (not the batch id). What
do
I
do now? How do I use multiple strings in a combo box to limit records
in
a
report but not output that field in the report itself?


:

Perfect!!! I knew I must have been missing something with the [Notes]
reference. Thank you all!

:

You must have the name of the field against which you're comparing:
that's
what Allen meant by [Notes] in his original example. Replace [Notes]
with
the appropriate field name.

The string you use as a filter must repeat the name of the field for
each
comparison. That means you need something like:

Me.Filter = "([Notes] Like '*0506*') OR ([Notes] Like '*0517*') OR
([Notes]
Like '*0515*') OR ([Notes] Like '*0516*')"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I can not use the multi-select list box - there are just too many
user
defined possibilities (although I printed the article for a
different
project
- thanks.)

I tried the code you provided for multiple keywords and I had two
issues

1) the code... strWhere = strWhere & "([Notes] Like ""*" &
strWord &
"*"") OR " ...what does the [Notes] mean? For it to work I had
to
revise
the code to be... strWhere = strWhere & "Like ""*" & strWord &
"*""
OR "
(Per the Locals window this is working fine)

2) When I try to run the code and get to the line "Me.Filter =
Left(strWhere, lngLen)" I get a run time error "run-time error
'2448': You
can't assign a value to this object." I can not figure out what
is
wrong...the Left function give the correct sting (I tested that
separately)

PS I made sure the form is set to allow filters, just in case that
was the
problem.


:

The query will not be able to interpret multiple criteria like
that, so
you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound
multi-select
list box on your form, and build the string as shown in this
article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a
report,
but
building the Filter for a form is identical.

Another option would be just to type all the values into a text
box
separated by spaces, and use code like this to build the filter
string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" &
strWord
&
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR
".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I have a form with a combo box (BatchID) that when changed will
populate
the
detail in the form. I want to be able to use multiple
wildcard
strings to
search the database for the detail. For example, I want to
enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the
BatchId
field. This works fine in the query directly, but when I put
the
text
in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to
the
BatchID
combo box.

 

[Douglas J Steele]

I don't understand what you're trying to do, then.

If you're wanting to limit what's reported, you have to somehow include the
criteria.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That is exactly my problem. I CAN'T add it to the query. The query is a
summary query and the filter will not work if I add the field as a where or
expression field and I CAN'T add it as a group or it will mess up the report
results.

My problem is I can not add the field to the query and I can not filter if
the field is not in the query.

My question is - How do I query for a report for multiple wildcards strings
in a text box when I can not have the text box field in the report's query?

Add the field to the query then. Just because a field is in the query
doesn't mean you have to display it on the form. However, if you want to
filter by it, it has to be in the query...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a summary report by BatchId and it is filtering fine. The detail
report that does not have BatchId in the output is giving me an error -
"Enter Parameter Value tblBatchPickList.BatchID"
(tblBatchPickList.BatchID
is the field I am trying to filter on.) I am assuming it is asking
because
it can not find that field.

The report's data is based on a embedded query that has tblBatchPickList
in
the table list, but not in the output of the query.

:

Just because the value doesn't appear on the report doesn't mean you
can't
use the field to filter what rows are in the RecordSource for the report.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


New spin on the old question...

I have a report that works off this same text box. The report uses
these
batch ids to LIMIT the records but it does not USE the batch id field
in
the
output so I can't filter by that field, right? The report takes these
multiple batch ids and cross references them to a materials list and
then
groups and subtotals by the raw material item (not the batch id). What
do
I
do now? How do I use multiple strings in a combo box to limit records
in
a
report but not output that field in the report itself?


:

Perfect!!! I knew I must have been missing something with the [Notes]
reference. Thank you all!

:

You must have the name of the field against which you're comparing:
that's
what Allen meant by [Notes] in his original example. Replace [Notes]
with
the appropriate field name.

The string you use as a filter must repeat the name of the field for
each
comparison. That means you need something like:

Me.Filter = "([Notes] Like '*0506*') OR ([Notes] Like '*0517*') OR
([Notes]
Like '*0515*') OR ([Notes] Like '*0516*')"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I can not use the multi-select list box - there are just too many
user
defined possibilities (although I printed the article for a
different
project
- thanks.)

I tried the code you provided for multiple keywords and I had two
issues

1) the code... strWhere = strWhere & "([Notes] Like ""*" &
strWord &
"*"") OR " ...what does the [Notes] mean? For it to work I had
to
revise
the code to be... strWhere = strWhere & "Like ""*" & strWord &
"*""
OR "
(Per the Locals window this is working fine)

2) When I try to run the code and get to the line "Me.Filter =
Left(strWhere, lngLen)" I get a run time error "run-time error
'2448': You
can't assign a value to this object." I can not figure out what
is
wrong...the Left function give the correct sting (I tested that
separately)

PS I made sure the form is set to allow filters, just in case that
was the
problem.


:

The query will not be able to interpret multiple criteria like
that, so
you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound
multi-select
list box on your form, and build the string as shown in this
article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a
report,
but
building the Filter for a form is identical.

Another option would be just to type all the values into a text
box
separated by spaces, and use code like this to build the filter
string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" &
strWord
&
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR
".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I have a form with a combo box (BatchID) that when changed will
populate
the
detail in the form. I want to be able to use multiple
wildcard
strings to
search the database for the detail. For example, I want to
enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the
BatchId
field. This works fine in the query directly, but when I put
the
text
in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to
the
BatchID
combo box.

 

[Guest]

Ok I have tried to understand whats going on here but getting a little confused

I have a Table with Products (text) then a correspnding form with a subform
where you can use a Combo box to select the product - then shows price/cost
and so on.

example product - Great big caravan

I want the combo box to limit the what is in the drop down list when I type
car - so like a query automaticly does the following *car* for me, but I have
become very confused with this post

Many thanks

The query will not be able to interpret multiple criteria like that, so you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound multi-select
list box on your form, and build the string as shown in this article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report, but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a combo box (BatchID) that when changed will populate
the
detail in the form. I want to be able to use multiple wildcard strings to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the BatchId
field. This works fine in the query directly, but when I put the text in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the BatchID
combo box.

 

[BruceM]

OK, you have a Products table and a corresponding form, but what is the
subform? What is the purpose of the Products form? Is the combo box a
search combo box (to find a particular record), or is it to add Product
information to another record (such as Invoice)?
The combo box can be made to limit the list that drops down, but in what way
do you wish to limit it?

Ok I have tried to understand whats going on here but getting a little
confused

I have a Table with Products (text) then a correspnding form with a
subform
where you can use a Combo box to select the product - then shows
price/cost
and so on.

example product - Great big caravan

I want the combo box to limit the what is in the drop down list when I
type
car - so like a query automaticly does the following *car* for me, but I
have
become very confused with this post

Many thanks

The query will not be able to interpret multiple criteria like that, so
you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound
multi-select
list box on your form, and build the string as shown in this article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report, but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a combo box (BatchID) that when changed will populate
the
detail in the form. I want to be able to use multiple wildcard strings
to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the BatchId
field. This works fine in the query directly, but when I put the text
in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the BatchID
combo box.

 

[Guest]

Ok I have a products table, customerproducts table then a customer table

The customerproducts table looks up data in the product table - ref
productID in both tables
The customer table and the customerproducts table contains custID as a ref

There fore I have productID and CustomerID in there respective tables

My form has been limited to customers using a combo/list selection box -
then my subform uses customerproducts to show product, and price - using
original data retrieved from products table.

Now I select my customer which shows address tel etc. I then choose the
product description box (on the subform) to add a product related to that
customer. I have 700 products and wish to type anypart of the name in the
combo box and it show only those records that match so I can then choose it

Customer - CustID, Name, Tel and so on
CustProduct - CustID, ProductID, CustPrice
Product - ProductID, Description, List Price, Cost Price

so the productID in CustProduct retrieves the description fields up so I can
show it in my subform then I can enter the customers unique price for that
item.

thanks for the reply

OK, you have a Products table and a corresponding form, but what is the
subform? What is the purpose of the Products form? Is the combo box a
search combo box (to find a particular record), or is it to add Product
information to another record (such as Invoice)?
The combo box can be made to limit the list that drops down, but in what way
do you wish to limit it?

Ok I have tried to understand whats going on here but getting a little
confused

I have a Table with Products (text) then a correspnding form with a
subform
where you can use a Combo box to select the product - then shows
price/cost
and so on.

example product - Great big caravan

I want the combo box to limit the what is in the drop down list when I
type
car - so like a query automaticly does the following *car* for me, but I
have
become very confused with this post

Many thanks

The query will not be able to interpret multiple criteria like that, so
you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound
multi-select
list box on your form, and build the string as shown in this article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report, but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a combo box (BatchID) that when changed will populate
the
detail in the form. I want to be able to use multiple wildcard strings
to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the BatchId
field. This works fine in the query directly, but when I put the text
in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the BatchID
combo box.

 

[BruceM]

If I understand correctly, CustProduct is a junction table between Customer
and Product. A typical setup is a main form based on the Customer table and
a subform based on CustProduct. A combo box on the subform is bound to
ProductID; its row source is based on the Product table.
Setting the combo box Auto Expand property to Yes may do what you need. If
your intention is to filter a very large list according to the first few
characters you type into the combo box, see:
http://allenbrowne.com/ser-32.html

For more information about combo boxes:
http://allenbrowne.com/ser-03.html

Ok I have a products table, customerproducts table then a customer table

The customerproducts table looks up data in the product table - ref
productID in both tables
The customer table and the customerproducts table contains custID as a ref

There fore I have productID and CustomerID in there respective tables

My form has been limited to customers using a combo/list selection box -
then my subform uses customerproducts to show product, and price - using
original data retrieved from products table.

Now I select my customer which shows address tel etc. I then choose the
product description box (on the subform) to add a product related to that
customer. I have 700 products and wish to type anypart of the name in the
combo box and it show only those records that match so I can then choose
it

Customer - CustID, Name, Tel and so on
CustProduct - CustID, ProductID, CustPrice
Product - ProductID, Description, List Price, Cost Price

so the productID in CustProduct retrieves the description fields up so I
can
show it in my subform then I can enter the customers unique price for that
item.

thanks for the reply

OK, you have a Products table and a corresponding form, but what is the
subform? What is the purpose of the Products form? Is the combo box a
search combo box (to find a particular record), or is it to add Product
information to another record (such as Invoice)?
The combo box can be made to limit the list that drops down, but in what
way
do you wish to limit it?

Ok I have tried to understand whats going on here but getting a little
confused

I have a Table with Products (text) then a correspnding form with a
subform
where you can use a Combo box to select the product - then shows
price/cost
and so on.

example product - Great big caravan

I want the combo box to limit the what is in the drop down list when I
type
car - so like a query automaticly does the following *car* for me, but
I
have
become very confused with this post

Many thanks

:

The query will not be able to interpret multiple criteria like that,
so
you
will need to generate the filter string in code.

For individual values (not wildcards), you could use an unbound
multi-select
list box on your form, and build the string as shown in this article:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
The article explains how to build the WhereCondition for a report, but
building the Filter for a form is identical.

Another option would be just to type all the values into a text box
separated by spaces, and use code like this to build the filter
string:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" &
strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a combo box (BatchID) that when changed will
populate
the
detail in the form. I want to be able to use multiple wildcard
strings
to
search the database for the detail. For example, I want to enter

*0506* or *0515* or *0516*

and get all records where there is a 0506, 0515 OR 0516 in the
BatchId
field. This works fine in the query directly, but when I put the
text
in
the
combo box, it will not bring back the data.

I have tried
[Forms]![frmBatch Pick List]![BatchID]
Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*"
Like [Forms]![frmBatch Pick List]![BatchID]

but they do not work if I add multiple wildcard requests to the
BatchID
combo box.