Filter recordset problem error 3001

Still doesnt work, could it be the data i'm using

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

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.

Are you using ADO or DAO?

Im using ADO

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

If the field type is set as a number field then I think you need to remove the single quotes.

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.

 

So ,you also tried vinnyd79 suggestion?


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

Can you print the filter string again?

Also check that your field names that you are using in your filter are correct.

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

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?

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.

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?

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.