Query to accept partial keying of names

  • Thread starter Thread starter el zorro
  • Start date Start date
E

el zorro

I have a form where the user hits a control button to run
a query. The query reads the last name of a person from a
control on the form, then shows the corresponding files
for that last name.

It works fine, except that I have now been asked to
require only the first few letters from the user (lots of
the names are long and hard to spell), then to have the
query show all the names beginning with those initial
letters.

THe thing is, I want the query to read the name from the
form, and not have the user retype the name in response
to some kind of prompt.

SO, where so I begin? (If it makes it easer, the button
can open a report based on the query.) Is there a
parameter I can enter for the query, using some kind of
wild cards, that will read the first letters entered in
the FORM CONTROL and give all the names that begin with
these letters?

THanks in advance--- you all are GREAT!
 
el said:
I have a form where the user hits a control button to run
a query. The query reads the last name of a person from a
control on the form, then shows the corresponding files
for that last name.

It works fine, except that I have now been asked to
require only the first few letters from the user (lots of
the names are long and hard to spell), then to have the
query show all the names beginning with those initial
letters.

THe thing is, I want the query to read the name from the
form, and not have the user retype the name in response
to some kind of prompt.

SO, where so I begin? (If it makes it easer, the button
can open a report based on the query.) Is there a
parameter I can enter for the query, using some kind of
wild cards, that will read the first letters entered in
the FORM CONTROL and give all the names that begin with
these letters?
Click to expand...


You can use the form's text box as a parameter in the query.
Set the criteria for the name field to something along the
lines of:

LIKE Forms!theform.thetextbox & "*"
 
AH-- the & "*" was part of what I was misssing. But I
need a further refinement. Let's say the name on the form
control is "Lincoln." I want the query to show all names
that begin with "Lin." Can this be done? Maybe with some
kind of function that truncates the name to the first 3
letters for the query? THen maybe I could write something
like Function([name]) & "*"?

Thanks.
 
el said:
AH-- the & "*" was part of what I was misssing. But I
need a further refinement. Let's say the name on the form
control is "Lincoln." I want the query to show all names
that begin with "Lin." Can this be done? Maybe with some
kind of function that truncates the name to the first 3
letters for the query? THen maybe I could write something
like Function([name]) & "*"?
Click to expand...

LIKE Left(Forms!theform.thetextbox, 3) & "*"
--
Marsh
MVP [MS Access]


 
Very cool-- thanks!

BTW-- is there a book that lists all the functions
available in Access, with an explanation of the arguments
and a narrative description of what the functions do? I
would probably pay actual money for such a book!
-----Original Message-----
el said:
AH-- the & "*" was part of what I was misssing. But I
need a further refinement. Let's say the name on the form
control is "Lincoln." I want the query to show all names
that begin with "Lin." Can this be done? Maybe with some
kind of function that truncates the name to the first 3
letters for the query? THen maybe I could write something
like Function([name]) & "*"?
Click to expand...

LIKE Left(Forms!theform.thetextbox, 3) & "*"
--
Marsh
MVP [MS Access]


from
a (lots
of along
the
Click to expand...
.
Click to expand...
 
I think the best list is in VBA Help:

Table of Contents
Visual Basic Language Reference
Functions
--
Marsh
MVP [MS Access]


Very cool-- thanks!

BTW-- is there a book that lists all the functions
available in Access, with an explanation of the arguments
and a narrative description of what the functions do? I
would probably pay actual money for such a book!
-----Original Message-----
el said:
Let's say the name on the form
control is "Lincoln." I want the query to show all names
that begin with "Lin." Can this be done? Maybe with some
kind of function that truncates the name to the first 3
letters for the query? THen maybe I could write something
like Function([name]) & "*"?
Click to expand...

LIKE Left(Forms!theform.thetextbox, 3) & "*"
Click to expand...
Click to expand...
 
Back
Top