Parameter Query

I have a query setup for the user to search for a person using the lastname
field. What I would like to do is narrow it down further by having the query
ask the user to input the lastname and then the first name. Thus bringing up
only the records matching that particular person.

Is this possible?

Scott

Scott

Possible, yes. Desirable, not much!

If you want your users to find a particular person, why are you forcing them
to remember the person's name, AND to spell (and punctuate) it properly?

If you create a form, add a combobox, and base that combobox on a query that
lists all available persons by LastName, FirstName, the users will be able
to PICK the one they want.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Srowe" wrote in message
...
I have a query setup for the user to search for a person using the lastname
field. What I would like to do is narrow it down further by having the
query
ask the user to input the lastname and then the first name. Thus bringing
up
only the records matching that particular person.

Is this possible?

Scott

On Fri, 6 Feb 2009 14:35:01 -0800, Srowe
wrote:

I have a query setup for the user to search for a person using the lastname
field. What I would like to do is narrow it down further by having the query
ask the user to input the lastname and then the first name. Thus bringing up
only the records matching that particular person.

Is this possible?

Scott

A "Parameter Query" is the technique you'ld use. Bear Jeff's good advice in
mind - selecting "Ravindranathan, Srinivasan" from a combo box is easier than
typing it! But you can use a paramter query with criteria such as

=[Enter last name:]

on the last name field, and also

=[Enter first name:]

on the first name field. Better would be to have an unbound Combo Box cboName
on a form (let's call it frmMyForm); this would have the unique personID as
its bound column, and you'ld use a criterion on the PersonID field of

=[Forms]![frmMyForm]![cboName]

If your names table doesn't have a unique person ID... it should. Names change
and they are not unique.

--

John W. Vinson [MVP]

Jeff,

Can this work if the first and last names are separate fields?

If a table had the 2 fields, could a query have the combo box like that?

I work mostly in a query.

TIA
Rich W.


Jeff Boyce wrote:

If you create a form, add a combobox, and base that combobox on a query that
lists all available persons by LastName, FirstName, the users will be able
to PICK the one they want.

Open query in design view. Type this in the Criteria row of the grid under
field FirstName.
Like [Enter first name] & "*"

This allows the operator to enter complete name, forst part of name, or no
name by pressing the ENTER key.

--
KARL DEWEY
Build a little - Test a little


"Srowe" wrote:

I have a query setup for the user to search for a person using the lastname
field. What I would like to do is narrow it down further by having the query
ask the user to input the lastname and then the first name. Thus bringing up
only the records matching that particular person.

Is this possible?

Scott

"John W. Vinson" wrote:

On Fri, 6 Feb 2009 14:35:01 -0800, Srowe
wrote:

I have a query setup for the user to search for a person using the lastname
field. What I would like to do is narrow it down further by having the query
ask the user to input the lastname and then the first name. Thus bringing up
only the records matching that particular person.

Is this possible?

Scott

A "Parameter Query" is the technique you'ld use. Bear Jeff's good advice in
mind - selecting "Ravindranathan, Srinivasan" from a combo box is easier than
typing it! But you can use a paramter query with criteria such as

=[Enter last name:]

on the last name field, and also

=[Enter first name:]

on the first name field. Better would be to have an unbound Combo Box cboName
on a form (let's call it frmMyForm); this would have the unique personID as
its bound column, and you'ld use a criterion on the PersonID field of

=[Forms]![frmMyForm]![cboName]

If your names table doesn't have a unique person ID... it should. Names change
and they are not unique.

--

John W. Vinson [MVP]

All good suggestions. I will play around with which one works best for my
situation. Just a sidenote. In this particular search of the subject you
already know his full name. I do like the combobox idea though.

Thanks to everyone.

Scott

Thanks Jeff.

One issue I am having is that upon picking which name you want not all of
the associated fields are filling in. In particular The S1Sex field and the
S1Maidenname field. They are in the query but they are not showing up in the
drop down of the combobox when I type in the Surname.

Any suggestions?

Scott

"Jeff Boyce" wrote:

Scott

Possible, yes. Desirable, not much!

If you want your users to find a particular person, why are you forcing them
to remember the person's name, AND to spell (and punctuate) it properly?

If you create a form, add a combobox, and base that combobox on a query that
lists all available persons by LastName, FirstName, the users will be able
to PICK the one they want.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Srowe" wrote in message
...
I have a query setup for the user to search for a person using the lastname
field. What I would like to do is narrow it down further by having the
query
ask the user to input the lastname and then the first name. Thus bringing
up
only the records matching that particular person.

Is this possible?

Scott

On Sat, 7 Feb 2009 07:36:01 -0800, Srowe
wrote:

In this particular search of the subject you
already know his full name. I do like the combobox idea though.

That's an even stronger reason to use the combo box, just to save typing: with
the default Autocomplete property set the user need only type the first few
letters, and needn't worry about spelling errors.
--

John W. Vinson [MVP]

The whole reason for using a query here is because you can create a new
"field" in the query:
NewField: [LastName] & ", " & [FirstName]

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Rich Wonneberger" wrote in message
...
Jeff,

Can this work if the first and last names are separate fields?

If a table had the 2 fields, could a query have the combo box like that?

I work mostly in a query.

TIA
Rich W.


Jeff Boyce wrote:

If you create a form, add a combobox, and base that combobox on a query
that lists all available persons by LastName, FirstName, the users will
be able to PICK the one they want.