Question Search????

In my database I want a form which allows me to serach for a feild in a table.

The table i want to serach in is titled Trader Details and the field that i want the user to type in to search is First Name and Second name.

I want the user to type either the first name or last name in and click search and then all the details in the table apppear below. Can i do this using macros and querys or do i have do use a SQL or Vb code?
I hope i have explained this in the right way.

Cheers Sam

This is the method I use to search from a form.
There are other ways of doing the same thing, but I have been doing it this way for years and its never let me down.

Create a new query for your table.
In the first name criteria field enter:
[Enter First Name] - square brackets required.
Do the same with the last name (using Last Name)

Rn the query. It will prompt you for the first and last name. Enter them and press "OK". All of the records with that name should be returned.

Now create a form with the first and last name fields. Call the form "NameSearchFrm". Rename the fields on the form to "FirstNameSearch" and "LastNameSearch". Add a command button onto the form to open the query you just created.

In the first and last name fields in the QUERY, change the criteria to:
[Forms]![NameSearchForm]![FirstNameSearch]
[Forms]![NameSearchForm]![LastNameSearch]

When you enter the names in the SearchFrm and click on the command button you should get the same results as you got earlier only this time there's no prompt for the names, they were already entered in the form.
You can make the search fields on the form anything you want (start date/end date for example). Just change the criteria in the query to represent the names of the text boxes on the search form. You should always use a name for the text box that is similar but not exactly the same as the field name (notice I like to put search into the text box name - my personal foible).

In a nutshell, this is my method of searching from a form. You could reset the command button on the search form to open another form, a report or whatever you wish as long as it is being run from the query.

I personally use this method in a macro as it allows me to close the search form after it has found the data I require.

Mate...

Thanks alot worked a treat cheers.

One other question.

At the moment this requires the user to serach via both fields, how can i make so if the user only knows one of either the first name or second name it will show a list of results...

for example if the user serachs john he will get many results but if he serachs John Smith, he will only get one.

Sam

I would like to ask a question as well, how would you work around empty fields in the search? I have tried this method and an empty field does not get reported back. I have used Nz(), IsNull or maybe I am not working them correctly. Currently my query search is like this--- like "*" & [Forms]![search1].[name1] & "*" ---- This will not return any "blank" files with multiple search items with the same type of setup.

samgfi said:

One other question.

At the moment this requires the user to serach via both fields, how can i make so if the user only knows one of either the first name or second name it will show a list of results...

for example if the user serachs john he will get many results but if he serachs John Smith, he will only get one.

Sam

Click to expand...

OK Sam, bear with me.

You can do the same thing as previous, except in the query, create a new field by placing the cursor in the blank field at the far right. We'll call this field FULLNAME. Enter:
Fullname[firstname]&[lastname])
Run the query and you will see that the names in your table have been connected into one field (John Smith for example).

Go back to your search form, or create a new one with just one textbox. Name the textbox namesearch
Place on command button on the form to open your new query.
In the criteria of the query in the Fullname field enter:
[Forms]![FormName]![namesearch]

Should work OK

As stated earlier there are other ways to do this (Majicman's post above). I am assuming you are an Access newbie so I'm keeping it simple.

Note: after Fullname above there is a purple face. This should be a colon followed by a ( with no space.

I cant get the first part to work, it keeps saying that there is an "invalid
.(dot) or! operator or invalid parentheses."


thanks for your help, yeah i am very new to this.