B.Termeer B.Termeer - 11 months ago 60
SQL Question

Access SQL: like function on a number field

I have ,for example, this table in a Microsoft Access database:

id numeric
context text
numberfield numeric

I want to select every record that ends with 9 in the column"numberfield". This gives a problem because it is a numeric field and as a result I can not use the following SQL:

select * from table where numberfield like "%9"

A solution is that I change the numberfield to a text. But this gives a problem because there are several users and the change might give a problem in the future. Is there an option to select on the ending when it is a number field?

Answer Source

That sound a little fishy.. are you sure you can use that query? Don't know about Access but almost any other DBMS allows it.

If it really doesn't work, you can do this:

select * from table where STR(numberfield) like "*9"

EDIT: Maybe it didn't work because you used % which is used with * in Access .