Confused by "Like" operator

[cleanzero]

Hi,
to my understanding the Like operator used inside queries is intended to perform pattern matching on string fields. I'm very surprised to discover that in Access 2007 it works also on numeric fields. For example, I created a table with a numeric field called Data containing the values 2012,2013,2110 and realized that the following queries do work



SELECT myTable.data
FROM myTable
WHERE (((myTable.data) Like "2013"));


SELECT myTable.data
FROM myTable
WHERE (((myTable.data) Like "201*"));

SELECT myTable.data
FROM myTable
WHERE (((myTable.data) Like 2013)); //this one works even without "

Is this the way the Like operator is intended to work ? Does it perform pattern matching on numbers ? I'm confused.

[rpeare]

I don't know that I would do it that way (trying to recognize a string within a number)

I would likely try a combination of CSTR and INSTR

something like

SELECT myTable.data
FROM myTable
WHERE (instr(cstr(mytable.data), "SEARCHSTRING") >0);

To be honest I have never tried to do searches for partial number matches for instance if you were looking for 3.14 and you had


2003.14
13.14
012873401982734874019823473.14

should all be captured.

you'd only know if your current code worked by trying a bunch of variations to try and screw it up.

[cleanzero]

I never would have bet for my queries to work. I would have done it with a CSTR conversion. The problem raised from the fact that on an exam test I asked my students to write a query to select numeric fields different from 2013. I expected <>2013 as the correct answer but some of them answered NOT Like "2013*" which I marked as wrong, but to my surprise works!!!!!

[rpeare]

I would still mark it as wrong or at best only partial credit because, realistically, they got lucky!

[Xipooo]

Yeah I wouldn't rely on it, especially since that won't convert to a LIKE with Oracle, MSSQL, or MySQL. They'll all throw a fit, and perhaps later versions of Access won't like it either. Haven't tried it with 2013 yet, but it may have already been depreciated.

[cleanzero]

Damn Microsoft :-(