Creating user friendly search

[Guest]

Now I have to make a field in a table more searchable
I have a Last name field that is part of search.
the whole search criteia involves the enitire table. so when you select the
search button all the fields are viewable to scroll through.
I want to be able to type part of the last name and have my options then
presenteed to me.
Do I need to mak a new query with just the last name in it or can I modify
the existing table properties for that column?

 

[Guest]

Do not do anything to the table. Use a form with a Combo box and the Auto
Expand property set to Yes.

 

[Guest]

Karl:
My ecxperience with access has been on a mainframe and mainly with just
interacting with it.
So I create a from add a combo box w/expand set to yes.
How do I link my table to it? Do I use a command function and if so what
kind of arguments do I need to put in?
Thank you

 

[John W. Vinson]

Now I have to make a field in a table more searchable
I have a Last name field that is part of search.
the whole search criteia involves the enitire table. so when you select the
search button all the fields are viewable to scroll through.
I want to be able to type part of the last name and have my options then
presenteed to me.
Do I need to mak a new query with just the last name in it or can I modify
the existing table properties for that column?

Create a Parameter Query based on the table.

Include whichever fields you want to see when the search is successful.

Put

LIKE "*" & [Enter last name or part of it:] & "*"

on the Criteria line under the last name field.

Open the query and respond to the prompt. If you reply "wa" you'll find
yourself, Daddy Warbucks, Barbara Walters, and Nelson Seward; if you leave it
blank you'll find all records in the table.

John W. Vinson [MVP]

 

[Guest]

when you select the search button all the fields are viewable to scroll
through.
Let's back up a bit. What window is open when you 'select the search
button'?
Where is the 'search buuton' you are selecting? Are you in the table or what?

 

[Guest]

Karl:
When I open the application I get the main page,which is a Form. On the
main page are 2 buttons to choose from. One button lets you enter a new
patient the other lets you select exsisting patients.
I do not know how the main page is linking to the other 2 forms.
When I right click on the main form it does not give me an option to look
at the design view.

 

[Guest]

John:
Thank you it wporked like a charm and I added it to another portion of the
query
to allow results to be brought back for names on different columns
Can you explain your statement:
LIKE "*" & [Enter last name or part of it:] & "*"
so that I can under stand what the different elements are.

Now I have to make a field in a table more searchable
I have a Last name field that is part of search.
the whole search criteia involves the enitire table. so when you select the
search button all the fields are viewable to scroll through.
I want to be able to type part of the last name and have my options then
presenteed to me.
Do I need to mak a new query with just the last name in it or can I modify
the existing table properties for that column?

Create a Parameter Query based on the table.

Include whichever fields you want to see when the search is successful.

Put

LIKE "*" & [Enter last name or part of it:] & "*"

on the Criteria line under the last name field.

Open the query and respond to the prompt. If you reply "wa" you'll find
yourself, Daddy Warbucks, Barbara Walters, and Nelson Seward; if you leave it
blank you'll find all records in the table.

John W. Vinson [MVP]

 

[John W. Vinson]

Can you explain your statement:
LIKE "*" & [Enter last name or part of it:] & "*"
so that I can under stand what the different elements are.

See the online help for LIKE for the authoritative word, but...

LIKE is a search operator. The = operator looks for exact matches; the >=
operator looks for values greater than or equal to the criterion; the LIKE
operator looks for approximate matches. When you use LIKE, certain characters
are treated as "wildcards", making it possible to find text within a field
without having to exactly match the whole field.

The * wildcard means "Match any string of characters", so a criterion of

LIKE "*z*"

means to find all records where the field contains any string of characters
(including an empty string of no characters at all), followed by the letter z,
followed by any other string of characters; as a criterion it would hit on

adze z zzzzz quiz

and any other string containing a z.

A criterion of

LIKE "*" & [Enter last name or part of it:] & "*"

prompts the user with a Parameter (the stuff in brackets is the prompt);
whatever they type gets concatenated using the & string concatenation
operator, so that if the user sees "Enter last name or part of it:" and types
z, the criterion will become

LIKE "*z*"

Other wildcards include ? which matches any single character, # which matches
any numeric digit, etc. - see the help file.

John W. Vinson [MVP]

 

[Guest]

Thank you!

Can you explain your statement:
LIKE "*" & [Enter last name or part of it:] & "*"
so that I can under stand what the different elements are.

See the online help for LIKE for the authoritative word, but...

LIKE is a search operator. The = operator looks for exact matches; the >=
operator looks for values greater than or equal to the criterion; the LIKE
operator looks for approximate matches. When you use LIKE, certain characters
are treated as "wildcards", making it possible to find text within a field
without having to exactly match the whole field.

The * wildcard means "Match any string of characters", so a criterion of

LIKE "*z*"

means to find all records where the field contains any string of characters
(including an empty string of no characters at all), followed by the letter z,
followed by any other string of characters; as a criterion it would hit on

adze z zzzzz quiz

and any other string containing a z.

A criterion of

LIKE "*" & [Enter last name or part of it:] & "*"

prompts the user with a Parameter (the stuff in brackets is the prompt);
whatever they type gets concatenated using the & string concatenation
operator, so that if the user sees "Enter last name or part of it:" and types
z, the criterion will become

LIKE "*z*"

Other wildcards include ? which matches any single character, # which matches
any numeric digit, etc. - see the help file.

John W. Vinson [MVP]