ms access best way to create a search form

[MadSma]

hi iam quite new to access and i am now at a stage where i have created my database tables and various forms to input data on them but i need a way of displaying data based on some search criteria

for this i have created a form with various unbound fields but i do now how to proceed.

i also have create a report based on the table i want to display results from that is now just displaying all records

how should i proceed?
can the search find similar data except for identical data in the fields

[Mr Key]

Hi Madsma, Welcome to the BYTES Forum, this forum is for developers and ordinary users, Be free to ask in details, Speak exclusively what exactly you want to do. You may not receive enough response simple because your question is not clear. I suppose you ask your question again, this time include tables, search criteria, and all hints that should you think mandatory to solve your problem. For this question type I can tell you go to the web pages below that may help you.
If you give further details, I can assure you will receive amazing answers that you might not even thinking of.

http://bytes.com/topic/access/answers/696948-how-create-search-form-database

[MadSma]

hi Mr Key thank you for replying and sorry for that post i wasnt sure i could asked using so many details.
ok here i go again

i have a table containing my data called dataSource
it contains the field ID(primary key),name,age,d ate,team
i also have a form called dataEntry using the form wizard that enable me to enter data into my table(datasourc e)

i also created a report called report1 using the report wizard that displays all fields of each record line by line

What i want to do is to create some sort of search form that when the users enters some values of his choosing the report report1 will open containing only the hits.
About the Hits: I need to be able to enter data in any or all the fields in the search form and get matching results in the report but i need to search for similar matches beside exact matches. So if i only enter Andrew in the name field and hit the search button the report1 should open displaying all the records that contain in the name the text Andrew
like: Andrew Johnson
Andrew.O.Grimes
or even Sir Andrew Loyd

For the search form i have created a form with unbounded fields called searchForm that contains the fields:name,age ,date and team(all of which are data existing in my table)
and a button called search but i do not know how to proceed

ps:I need for some more functionality but I want to deal with each task at a time

[Mr Key]

Well Madsma, Try to use this findrecord form, import it to your database, you will be able to search from all queries and Tables in your database and create a new query from which you may create a report.
To search for the records that you are not sure, you may write in the criteria as follows
1. *xxx*? if you meant for the records that might contain ...xxx...
2. *xxx if it contain ...xxx
3. xxx* if it contains xxx...
4. xxx if you mean the whole word
5. *? for all records in that particular table/query
Open the Attached Search form to see if it can help you.
For further information about this form, open the link below!
Open this link to view the source code of this SEARCH FORM

[MadSma]

thanks alot this is very helpful as to create the search but i think i still have implementation problems

Here is what i did
i exported the form to my db and i then used it to create an sql statement
Select * from [Table1] Where [age] Like "*sage*?" OR [name] Like "*sname*?" OR [team] Like "*steam*?"

where sage,sname and steam are the filed on my searchform

and i used it to create a query but pasting Select * from [Table1] Where [age] Like "*sage*?" OR [name] Like "*sname*?" OR [team] Like "*steam*?" in the sql view of the query build but is not working
how can I create this query to take the values of my searchForm??
If I do that the I think the next step is to create a report based on that report and the use that to display my results when the serach button is presses… am I right?

[MadSma]

i just saw that i used Table1 instead of [datasource] but it is not working even when i use [datasource]

[malcolmk]

search based on unbound form

Hi, ok an easy to follow example.
I have a table called client. Fields are as follows
fname : first name
sname : surname
age: age
country : country

I then make a form (frmclient)usin g wizard or not for table client.
MAKE SURE that the text boxes are unbound (use the properties box).
Now using properties box make the default value of each textbox * (this gives us a wildcard in our search).

Ok, use the wizard to create a query of client and select option to modify query in wizard.

in the criteria box for fname enter "Like([forms]![table1].[controls]![fname].[value])" WITHOUT the quotes.
do this for each field replacing fname with the field name sname, age and country.
Save the query as qryclient.
create a form with wizard based on qryclient and save as frmresult.

Now load frmcountry in design view, add a command button and set to open form, select frmresult as the form to open.
Now run the form and enter any combination of data in any textbox but, be sure to leave the * in the fields that are for wildcard.

[MadSma]

just few clarification
when creating the query and entering Like([forms]![table1].[controls]![fname].[value])in the criteria
table1 is the name of my table and in the examble cases should it be clien?
also what should i write in the place of [value]

and a thrid question what is frmcountry did you mean the frmclient?

[malcolmk]

Hi, sorry got a bit messed up with rable name between yours and mine. The word value including square brackets ( [value]) is what you type in, it means to look at the value entered in control fname on form table1. I have attached a demo for you so you can see exactly what I mean.

[MadSma]

thanks for all the help i feel im getting closer to the finish line
i have used malcomk way and while trying to use the query to search within a table based on a form input i cant get it to work

in the criteria of the query i write select.... from....

WHERE (((tblregistry. name) Like [Forms]![searchForm]![txttitle]+'*'));

this works for searches abcXXX but how would i include the XXXabcXXX form

i tried
WHERE (((tblregistry. name) Like [Forms]![searchForm]!'*'+[txttitle]+'*'));
but it is not working any thoughts???

also could anyone tell me what is best to use
Like ([forms]![searchForm].[controls]![txttitle].[value]+'*')
or
Like [Forms]![searchForm]![txttitle]+'*'));

ie the actual text field name or the [value] field

[malcolmk]

try WHERE (((tblregistry. name) Like '*'+[Forms]![searchForm]![txttitle]+'*'));
Place a wild card in front of as well as behind your search text.

[MadSma]

thank you malcolml that did the trick