Searching an Access database field

[kevinj]

I'm setting up a search function using ASP for an Access database and will be allowing users to enter an entire
word(s)or partial into a textbox to search for in a specific field called COMPOSER.

What kind of select statement would I use so that the term(s) or partial term that they enter could be used to search any part or all of a specific field.

Set RS = DB.Execute("SELECT * FROM CONCERT_SELECTIONS WHERE _______________ ORDER BY COMPOSER ASC")

Thank you.

[bmcgee]

Set RS = DB.Execute("SELECT * FROM CONCERT_SELECTIONS WHERE COMPOSER LIKE '*xyz*' ORDER BY COMPOSER ASC")

This would match anything with "xyz" in the composer field. 'xyz*' would find only items starting with 'xyz'.

I believe * is the wildcard in Access. Normally in SQL, you would use %...Access is, well, Access.

[WebJoe]

AFAIK the SQL-wildcard in Access is %, but in the user-interface search-mask one could use * since this is the standard wildcards in many searches (files etc.).

To make sure you get all the results I'd do a

UCASE(Table.Fields) LIKE '%" & UCase(Form.TextBox) & "%')

since, not like Access' user-interface search-mask, in SQL a comparison like this is case-sensitive.

[kevinj]

Thanks for everyone's help on this question. I ended up using WHERE COMPOSER LIKE '%" & SCOMPOSER & "%' and it works great.