Been using Access a long time, but pretty new at SQL Server.

I would like to do a search on a column that I have defined as an integer data type. I have a stored proc that is used as a list box's record source and as my user types in the search field, the matching records appear in the list box. If my user types in '12', I would like to pass the '12' to the variable in my stored proc and have any combination of '12' in that field (1212, 8123, 5612...you get the point) appear in the list box.

I have read that you cannot do any type of wildcard search on an integer field, and my attempts at using the '%' character did not work, my guess is for that reason. It is very important that I have this type of functionality, so I might consider converting the column to a varchar.

Any suggestions?

I don't have much SQL experience either, but the CAST command comes to mind:
CAST(Field_name AS Char(4))
You'd have to have some type of stored procedure that would look at the value in that field and have CAST convert it to a character string, then search that string with wild cards.
Hopefully greater minds will weigh in on this.
BTW what does that 12 represent? I'm just curious.

Try:

SELECT YourField FROM YourTable
WHERE PATINDEX('%12%', CONVERT(VarChar(10), YourField)) > 0

But it sounds like this field should not be numeric....


EDIT: CAST or CONVERT should both work.
2nd EDIT: The VarChar(10) should be adjusted for your maximum value of the number.

Cool, I'm going to try it.

I'd like to keep this field numeric - it is for an asset tag that will only be numeric and is the keyfield of my main table. The person who uses it does not understand the fact that you should only enter the numbers from the asset tag and tried to "take notes" in the asset tag field

If I can't get this to work, It's going to have to be a varchar with a constraint to make sure the data is only numeric. Just figured an integer field should take care of the problem easier

Thanks again!

This is a subject of much debate. The general consensus here at UA is that Primary Keys should have no meaning to the user. It should only be used as an internal DB tracking device. I would use an identity column as the primary key and manually generate the tracking number. There are many discussions in the UA archives that list reasons why your method should be avoided.