Microsoft Access problems

Paul Tergat

if this is in the wrong thread mods feel free to move


I'm having a couple of problems with a database in access


I first want to make a query that performs a certain search.
At the moment to allow the search to work i need to, for example, type in the whole address to be able to find the record i want, e.g. maythorne crescent, but i need it to find that record if i just type "cres" or "may" or "maythorne" etc etc

anyone any ideas? i cant seem to find the correct expression for it

Cheers

Duckjob

select * from [mytable] where [Address] like "*may*"

Should do it....

Paul Tergat

Duckjob wrote:

select * from [mytable] where [Address] like "*may*"

Should do it....

sorry maybe i wasnt clear - the "may" (or it could be "thorne" etc etc) bit is what the user will be typing into a form, so i cannot tell the query to just look up "may" - its user dependent based on what they type in

so i had this expression:

Like "%Forms![FrmAddress][Address]%" - so this looks up what is typed into the address text field in that form to produce the results. If the letter M is typed in then the query will find any address containing the letter m


this didnt work though

Duckjob

That was just to illustrate the construct of searching for an occurrence of substring within a field


In the real world, I guess you'd want to parameterise the query with something like:

PARAMETER SearchField text;
SELECT * FROM [MyTable] WHERE [Address] LIKE SearchField;

..though in that case you would need to pass SearchField into the query with a * at the start and at the end, for example:

*may*
*thorne*

etc.

Hope this helps.

EDIT: Sorry, just saw your reply above...

One way i can think of would be to create a hidden field to hold the search expression,

so for example, if you field is called txtAddress, have another field called txtSearchAddress, then program the change event of txtAddress with something like:

txtSearchAddress = "*" + txtAddress + "*"

, then you just use:

Like Forms![FrmAddress][txtSearchAddress]

..in your query.