Parameter Query

  • Thread starter Thread starter Srowe
  • Start date Start date
S

Srowe

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
 
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
Click to expand...

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.
 
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.
 
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.
 
John W. Vinson said:
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
Click to expand...

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.
Click to expand...

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
 
In this particular search of the subject you
already know his full name. I do like the combobox idea though.
Click to expand...

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.
 
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
 
Back
Top