Querying more than 1 field in a table?

[Bombdogs]

hi all,

ive been banging my head agaisnt this problem for some time now & feel its time to take advice.

im using access2000 & have a table of school videos & dvds that have been reviewed. the important field names for this problem are 'program name', 'dvd name', 'topics covered' & 'synopsis'.

what i need to do is search (filter) all the records for a keyword match in ANY of these fields. i dont want to use the find function, because that means i have to keep clicking 'find next' & dont get an overview of all the recrds with a match. i cant do it by filtering because that means i only get records returned that match the keyword in all the fields im searching.

i thought this would be easy - just goes to show how little i know!

any help is likely to save my monitor from the brick im currently weilding

many thanks,

PMF

 

[KenHigg]

If you are doing the search from a form, do a text box where the user types in the word, then build a query where you ref this text box in the criteria, placing the ref on different lines so that it will execute a series of 'or's. Then when the user clicks a 'look up' button, reset the forms recordsource to this query.

Make sense?
kh

 

[Bombdogs]

thanks for the quick reply Ken!

ok sort of making sense, ive added a text box to my form (says unbound in it at the moment).

then build a query where you ref this text box in the criteria

thats the bit im now struggling with. i can program a little VB so this shouldnt be too hard to get my head round, but im not sure where i build the query (what buttons to click in access to start the process) & how to ref the text box. is there a simple tutorial you can point me to?

thanks again,

PMF

 

[KenHigg]

On the database window, where your new tables and forms are, you select the Queries tab and create a new query there.

kh

 

[Bombdogs]

sorry Ken if it seems that your telling me quite simple stuff, i only have 'half a handle' on the situation at the moment, tho im learning fast!

ive created a new query in 'query design view' before, couldnt see any place to start writing code. so i decided to right-click & selected 'SQL View' -> this i presume is where i need to enter the code to build my query with reference to my text box. im currently faced with 2 lines of code

SELECT 
FROM Table1;

unfortunately i dont know SQL so am now stuck i called the textbox which i wish to access 'keyword' (which i set in the properties in the form design view). whats the format for the code i'll now need?

havent got to the

Then when the user clicks a 'look up' button, reset the forms recordsource to this query.

bit yet. one step at a time!

your help so far is greatly appreciated,

PMF

 

[KenHigg]

Maybe this will help:

Link


kh

 

[stephen81]

You don't need to know SQL. If you go back to Query Design View, add all the fields you want to bring back in your query, then in the criteria boxes underneath each field, type the criteria you need.
Have a look at this attachment, hopefully it will make it a bit clearer.

 

[Bombdogs]

thanks Stephen, thats near perfect!! my mistake was that i was putting the expressions on the same OR line - thinking that meant they'd be OR'd. didnt realise that they each have to have their own OR line! doh!

anyway - i say near perfect - ideally i'd like to be able to display the resulting records in the pre-formatted form that the query is run from, at the moment they pop up in a seperate table. is this possible?

thanks again to yourself & Ken for all the help here.

PMF

 

[stephen81]

anyway - i say near perfect - ideally i'd like to be able to display the resulting records in the pre-formatted form that the query is run from, at the moment they pop up in a seperate table. is this possible?

Just need to change the onclick event for the run query button so that it opens whichever form you need.

i.e.

stDocName = "Form1"
DoCmd.OpenForm stDocName, acNormal, acEdit

Instead of

stDocName = "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

and set the record source for the Form1 (or whatever it's called) to your query.