Variable search in Access

[Robertf987]

Hi, I need some help, I would be eternally greatful for any kind person out there to give me any advice.

I've created a database for the youth centre. It stores applications for funding. I have the table "tbl:Applicatio ns". And in that table there is a Notes field. I want to be able to do a search to bring up a list of all records containing certain words in the notes field. The thing is, I'm not sure on how to do this. I'm pretty sure the solution is either in an SQL query, or doing SQL from VBA.

I can get it to search for a specified word - for example if I want to search for all records containing the word "music", I have this SQL:

SELECT *
FROM [Tbl:Applications]
WHERE ((([Tbl:Applications].Application_Name) Like "*music*"));

And that works fine. But I want it to search for a variable. One time I might want it to search for "music", next I might want it to search for "art", or I might want it to search for "Young Mums Equipment" (so more than one word). I just want to know how to achieve this. In an access query you can have [insert text] which will bring up an input box labelled "insert text" and what you tye in that it will search for, BUT it will only search for exact matches, unless you type asterisks around your search text. Is there any way to automatically put asterisks around the search criteria in the SQL code to make it do a containing text search, or is there something to do in VBA to do this? I need some help and I'm not an expert, but I need to get this working.

[Rabbit]

SELECT *
FROM [Tbl:Applications]
WHERE ((([Tbl:Applications].Application_Name) Like "*" & [Search String] & "*"));

This will bring up an input box asking the user to define [Search String].

[Robertf987]

Rabbit, I only have one thing to say.... Will you marry me? Thank you! So simple. I had tried over and over and over again along those lines you showed me, but never got it quite right. I'd searched all over the net but couldn't find anything, I muddled around with code and actually got very, very close to what you put, but was getting muddled with apostrophes and quotation marks.

Thank you again for your help, and so quickly, you're a life saver! I give you this cookie in thanks *passes cookie*

[Rabbit]

You're welcome.

[NeoPa]

Rabbit, I only have one thing to say.... Will you marry me?

So which of you is the Groom and which the Bride? I hope you know - it could get confusing :D
You may find this link (Tutorials on Access and VBA) helpful as there are a number of helpful threads on some of the basics of Access.
I would just add that in SQL it is a good idea to get into the habit of using single-quotes (') for strings. This is explained further in one of the links found in the Tutorials thread.

[Robertf987]

Thanks NeoPa, I'll check out the link. Unfortunatley I don't think the marriage will go ahead. You see, I'm quite, quite insane. I've been learning most things in computing basically as and when I need them. I've made a fair few databases, both at school for projects and some actually in use, so I'm not exactly a newbie at all this. It just happens I never actually learned SQL at school, and I got a tad stuck. The database is now all complete and working, has nice lovley print functions to automatically merge stuff into word and print it all, (not exactly a mail merge, slightly different), search functions.... and it all looks very professional and pretty (I might get stuck with the programming, but making things look pretty and professional on the computer is something I do well!)
So thanks again for everybody's help. I shall stop now before I make myself look like a crazy person.
P.S. no cookies left.

[NeoPa]

If you want to blend in here - just act crazy :D