ADO Filtering using LIKE and numeric fields? 
Author Message
 ADO Filtering using LIKE and numeric fields?

I have a recordset that I want to be able to search. I set
the filter property of the recordset to:

"Variable LIKE '" & myVariable & "%'"

This works fine for string fields, but when I try it on a
numeric field it fails. This works on my numeric fields:

"Variable LIKE '1'", and so does:
"Variable LIKE '" & myVariable  & "'"

However, when I use the wildcard I get a filter cannot be
opened error.
An equivalent query directly against my SQL database
works. Is there some kind of change required to my code,
or can I not filter a recordset with a wildcard on a
numeric field?

Thanks for any help,
Ian



Wed, 09 Nov 2005 00:26:50 GMT  
 ADO Filtering using LIKE and numeric fields?
Hi,

No, you cannot use LIKE with wildcards against numeric fields. This by
design not just in ADO but in databases as well

--
Val Mazur
Microsoft MVP


Quote:
> I have a recordset that I want to be able to search. I set
> the filter property of the recordset to:

> "Variable LIKE '" & myVariable & "%'"

> This works fine for string fields, but when I try it on a
> numeric field it fails. This works on my numeric fields:

> "Variable LIKE '1'", and so does:
> "Variable LIKE '" & myVariable  & "'"

> However, when I use the wildcard I get a filter cannot be
> opened error.
> An equivalent query directly against my SQL database
> works. Is there some kind of change required to my code,
> or can I not filter a recordset with a wildcard on a
> numeric field?

> Thanks for any help,
> Ian



Wed, 09 Nov 2005 00:46:20 GMT  
 ADO Filtering using LIKE and numeric fields?

Quote:
> numeric field it fails. This works on my numeric fields:

> "Variable LIKE '1'", and so does:
> "Variable LIKE '" & myVariable  & "'"

Try
"char(Variable) LIKE '" & myVariable  & "%'"
char is a cast function in DB2.  try cstr for access

Be careful though:  This solution would require the query to convert every
value in the queried column to a string.  I dont think an index could be
used here (when casting).

Or you could formulate an equivalent numeric expression:
"Variable = 1 or " & _
"Variable between 10 and 19 or " & _
"Variable between 100 and 199 or " & _
"Variable between 1000 and 1999 or " & _
"Variable between 10000 and 19999 or " & _
"Variable between 100000 and 199999 or...
this method is about 13 times faster on my database.  I think the biggest
speedup comes from the 2nd method using an index.

Cheers
Stu



Sat, 12 Nov 2005 20:31:45 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. ADO Filter on a numeric field

2. how to update a Numeric field in a table using VB6 ADO

3. oracle numeric fields are empty when using ADO

4. Filtering ADO Recordset on Date Field using NON-US Locale

5. LIKE operator in filter property of recordset (for numeric fields)

6. convert text field to numeric field being used in Selection Formula

7. Assign a numeric variable to a numeric field name

8. OLE DB - ADO : dates and numeric fields

9. Oracle database Ado recordset insert null into numeric field

10. ADO Recordset Returns -1 for numeric fields

11. ADO Recordset Returns -1 for numeric fields

12. ADO Recordset Returns -1 for numeric fields

 

 
Powered by phpBB® Forum Software