filter form with combo box using Like operator

[hollyylloh]

I am interested in doing the following, and am open to any approach: I want
to filter a form for a specific account using a combo box.

The tricky part is that I want the combo box to not just look at the numbers
in the account in the order they appear, but to also filter the list as the
user types as the the value appears anywhere in the account number.

So for example, as the user types 122 the list is filtered for 1 then 12
then 122 etc. But I also want the list to be filtered for the sequence of
numbers type as they appear anywhere in the account number. So then, typing
122 filters for that sequence not just at the beginning of the account number
but also as it exists anywhere in the account number. The results would be
for example 1229567, 1229622, 3122333, 4412244, 5551225 etc.

The following code does not work but I am thinking that something similar
might. Perhaps there is something like a rs.findcontaining function?

Dim rs As Object
Dim myValue As Variant

myValue = "Like '*' & Me![FindAccount] & '*'"
Set rs = Me.Recordset.Clone
rs.FindFirst "[AccountID] = " & Str(Nz(myValue, 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Thanks for your time.

 

[John W. Vinson]

I am interested in doing the following, and am open to any approach: I want
to filter a form for a specific account using a combo box.

The tricky part is that I want the combo box to not just look at the numbers
in the account in the order they appear, but to also filter the list as the
user types as the the value appears anywhere in the account number.

So for example, as the user types 122 the list is filtered for 1 then 12
then 122 etc. But I also want the list to be filtered for the sequence of
numbers type as they appear anywhere in the account number. So then, typing
122 filters for that sequence not just at the beginning of the account number
but also as it exists anywhere in the account number. The results would be
for example 1229567, 1229622, 3122333, 4412244, 5551225 etc.

The following code does not work but I am thinking that something similar
might. Perhaps there is something like a rs.findcontaining function?

Dim rs As Object
Dim myValue As Variant

myValue = "Like '*' & Me![FindAccount] & '*'"
Set rs = Me.Recordset.Clone
rs.FindFirst "[AccountID] = " & Str(Nz(myValue, 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Thanks for your time.

You're using both the = operator (exact match) and the LIKE operator (wildcard
partial match). They don't mix - you need to use one or the other!

Try

MyValue = "[AccountID] LIKE '*" & Me!FindAccount & "*'"
rs.FindFirst MyValue