wildcard not working on text field with numeric data

[Tim_Mac]

hi,
i have an sql query that goes like this:
select * from Table1 where MyID LIKE '%05112%'

MyID is a text field, that stores numeric values. it is text because
there is a leading 0 on many of the values which must be preserved.

the above query does not return any results, despite a record existing
with MyID = 05112

the reason i use the like wildcard is because it is a dynamic query
builder that uses substring searches, and the user can search on any
field. the above syntax works properly for text fields that store
text.

my environment is c# / .net, and this is why i use the % wildcard
character instead of *. the above query works if use * wildcard within
access, but not when i use the .net built-in Jet driver.

does anyone understand why it does not work for numeric values?
thanks
tim

 

[Guest]

In the Access dialect of SQL, the wild character is *
It is % in SQL Server's dialect.
Incidentally, you don't need a text field to preserve leading zeroes. A
number field with a format of 0000000 will preserve as many digits as their
are 0's in the format field, supplying leading zeroes if necessary.

 

[Tim_Mac]

hi Ted,
thanks for the post. i understand that within access the wildcard
character is *. but when i execute this type of sql query using the
Jet 4 driver from asp.net, it does not work, whereas using % as a
wildcard does.

i guess it must be a Jet problem. i'll try posting on another
newsgroup.
thanks
tim