Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

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
0
octoped
Asked:
octoped
  • 8
  • 7
  • 2
2 Solutions
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now