Filter recordset problem error 3001

I'm having some filter problems. i've got a recordset that gets the data from the Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dealers.mdb;Persist Security Info=False...... etc and the recordsorce set to query1.

the data is mostly setup as text but there are some number coloumns.

i need to type the text/number to be filtered into text1 then the results appear into combo1. but i'm getting run-time error 3001. Is it something to do with the way its formated but I cant seem to fix it.

This is the code i'm using.



Private Sub Command1_Click()

Do Until dealers.Recordset.EOF

dealers.Recordset.Filter = dealers.Recordset.Fields(1).Name & " like '*" & UCase(Text1.Text) & "*' Or " & dealers.Recordset.Fields(2).Name & _
" like '*" & UCase(Text1.Text) & "*' Or " & dealers.Recordset.Fields(3).Name & " like '*" & UCase(Text1.Text) & "*'"

Combo1.AddItem dealers.Recordset.Fields(1) & " - " & dealers.Recordset.Fields(2) & " - " & dealers.Recordset.Fields(3) & " - " & _
dealers.Recordset.Fields(4) & " - " & dealers.Recordset.Fields(5) & " - " & dealers.Recordset.Fields(6)
         
   
dealers.Recordset.MoveNext

Loop

End Sub
octopedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vinnyd79Commented:
Does it work if you replace the "*" in the filter statement with "%"
0
octopedAuthor Commented:
Still doesnt work, could it be the data i'm using
0
ampapaCommented:
Have you tried adding a debug line before running your filter to see exactly what your SQL statement looks like?

sql = dealers.Recordset.Fields(1).Name & " like '*" & UCase(Text1.Text) & "*' Or " & dealers.Recordset.Fields(2).Name & _
" like '*" & UCase(Text1.Text) & "*' Or " & dealers.Recordset.Fields(3).Name & " like '*" & UCase(Text1.Text) & "*'"

debug.print sql
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

octopedAuthor Commented:
This is what the SQL statement looks like
DealerCode like '*4565*' Or DealerName like '*4565*' Or GroupCode like '*4565*'

not sure if it looks ok.
0
ampapaCommented:
Are you using ADO or DAO?
0
octopedAuthor Commented:
Im using ADO
0
ampapaCommented:
ADO I believe changed the wildcard to % from * as vinnyd79 suggested. Have you tried eliminating the last 2 or statements and tried only a single statement?


sql = DealerCode like '%4565%'
dealers.Recordset.Filter = sql
0
vinnyd79Commented:
If the field type is set as a number field then I think you need to remove the single quotes.
0
octopedAuthor Commented:
ok i've tried all the sugestions but not really getting anywhere. I can see from debuging the combo box that its getting data, but its just the filting that it doesnt like.

 
0
ampapaCommented:
So ,you also tried vinnyd79 suggestion?


fltr= "DealerCode like %4565%"
dealers.Recordset.Filter = fltr

Can you print the filter string again?
0
ampapaCommented:
Also check that your field names that you are using in your filter are correct.
0
octopedAuthor Commented:
Thanks for all the help I'm very nearly there,   I can get everything to filter apart from a part number,

Here's the code and my results of what works and what doesnt


fltr = "DealerCode like '4565'"                        ' works to filter an exact match for numbers
'fltr = "DealerCode like '%456%'"                   ' Doesn't work - I just get  error runtime error Filter Cannot Be Opened
'fltr = "DealerCode like %456%"                    ' Doesn't work - I just get  error runtime error Filter Cannot Be Opened
'fltr = "DealerName like 'Fred Motors'"            ' works for an exact match for text
'fltr = "DealerName like '%Fred%'"                 ' works for part match

dealers.Recordset.Filter = fltr

Do Until dealers.Recordset.EOF


Combo1.AddItem dealers.Recordset.Fields(1) & " - " & dealers.Recordset.Fields(2) & " - " & dealers.Recordset.Fields(3) & " - " & _
dealers.Recordset.Fields(4) & " - " & dealers.Recordset.Fields(5) & " - " & dealers.Recordset.Fields(6)              
           
dealers.Recordset.MoveNext

Loop

---------------------------------

So if anyone can suggest a way to get a part filter of a number field that would be great.

Many Thanks




0
octopedAuthor Commented:
I've now tried every combination that I can think of and if it helps I went into access and made a query just see what the sql statement looks like in access. Heres the access sql

SELECT Query1.DealerCode, Query1.*
FROM Query1
WHERE (((Query1.DealerCode) Like "*456*"));

well it works in access, so where am I going wrong?
0
ampapaCommented:
Access uses DAO or Jet and your using ADO which is why the query probably works in one language and not the other. The problem your having is definitely a syntax issue. Have you tried using an "_" as a wildcard? What type is the field "DealerCode" text or numeric?

This is from MS - http://support.microsoft.com/kb/q225048/ 

Wild Card Characters
The query wild-card characters are different in DAO than in ADO. DAO exposes the following characters for use with the SQL LIKE operator:

Character Function
* Match any string
? Match any character
# Match any digit
[a-cf] Match any of 'a' through 'c' or 'f'
[~a-c] Match anything but of 'a' through 'c'

ADO exposes the following ANSI wildcard characters:

Character Function
% Match any string
_ Match any character
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
octopedAuthor Commented:
the DealerCode field is numeric, and I have just tried the "_" option like this fltr = "DealerCode like _456_" (not sure if thats correct), but still no luck.



0
ampapaCommented:
Sorry running out of ideas. Did you also try the # and ? characters?

Is there a reason the field is numeric? Wouldn't that be better served as a textual field which would also fix your search problem?
0
octopedAuthor Commented:
I'm thinking that maybe you cant use wildcards on numerical fields. Anyway, i'll change all the  feilds to text instead (didnt want to originaly cos i'll have to re-do all the relationships again).
Thanks very much for all the help and getting the text filter working.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.