Queries Search Help

[Karliki]

Hey guys,

I could really use some help here. I haven't used Access since High School and have only recently had to use it again so I'm a little out of practice. I've created a form and table and filled in all the details and basically what I want Access to do now is allow me to search the data I've entered, via a text search. So basically I would like to open up a form type in what I want to find, access searches for it and brings up all entries what match that specific text search.

Is this possible and if so which area should I be looking at to create this.

Thanks for your help.

 

[DannySeager]

This is called Query By Form.

Create a query and add in your table... add in your fields and in the criteria of the one you want to search by add in

forms!YourSearchForm!TextBoxName

so your SQL for the query would look something like

SELECT * FROM YourTableName WHERE YourFieldName=forms!YourSearchForm!TextBoxName

This would give you exact matches though... so you may want to add in something like this into the criteria for the field you want to search

LIKE "*" & forms!YourSearchForm!TextBoxName & "*"

you've not said how you want to display these results though... you could create a report and make the query the data source for it... or a list box... or export to excel... if you post more detail then you will get a more detailed answer to that bit.

 

[Karliki]

Thanks for the help so far. I would like the results to be displayed in a report with maybe 5 results per page. Its basically a library catalogue so it has to search a title field and author field. I don't want the search information to be able to be edited just to be viewable. Hope this clears things up. If any other information is needed please let me know.

Thanks again for help.

 

[DannySeager]

Ok... so create a query with all the results in... create a report based on that query, create a form with controls on to filter the query and then add the criteria like I mentioned in my previous post to filter the query.

 

[Karliki]

Hey guys, I've tried to get the above working with the LIKE field as advised but am having problems getting the thing working. Basically I want a search option that will search within a title field. So for example, I might want to search a for a book called 'The fun we have in Access'. Now at the minute the only way I can find this book is typing in the exact title name into the input field. But what I need is it to be able to search individual words out within a field.

So if I just type the word 'fun' it will bring up all records with 'fun' in the title section. At the minute it would bring up nothing as its only searching for the full specific title not the words within the title. So how can I get it to search out specific elements out in a field?

The query I've done so far is this


SELECT CatalogueMain.BoxNo, CatalogueMain.BoxSection, CatalogueMain.Title, CatalogueMain.Notes
FROM CatalogueMain
WHERE (((CatalogueMain.Title)=[Search Word]));


So how can I change that to get it to search out words within a field.

Thanks again for any help

 

[CraigDolphin]

DannySeager already gave you the answer you need...

SELECT CatalogueMain.BoxNo, CatalogueMain.BoxSection, CatalogueMain.Title, CatalogueMain.Notes 
FROM CatalogueMain 
WHERE (((CatalogueMain.Title) Like "*" & [Search Word]] & "*"));

replace [Search Word] with the full reference to the text/combo box on your form as shown in Danny Seager's post.

 

[Karliki]

Thanks both for both the answers. They really helped.

 

[DannySeager]

you're welcome.

Just post back if you are still struggling.