Prev: Access Query Duplicate records
Next: Group Control Missing
From: PlarfySoober on 3 May 2010 17:06
I have a query which is basically an entire table with a calculated field.
The calculated field is "FirstName" + " " + "LastName", which are two fields
of the underlying table.

I wanted to search for a certain record, and the table isn't too large, so
tried it the simple way (this is in the Query Design Mode):

Field: FirstName
Table: RecruitingApplicants
Sort: Ascending
Show: Yes
Criteria: >=[Enter the First Letter of the Applicant's First Name.]

This works just fine when opening the query. But using a report OR a form
which is dependent on the query, it works sometimes, sometimes not, which I
find odd.

How can I get control of this? My table is getting larger and I'm scrolling
through quite a few records now.

Thanks.

Don.



From: KenSheridan via AccessMonster.com on 3 May 2010 18:21
The >= than operation will return names where the name sorts alphabetically
at or after the letter entered. I think what you are probably aiming for is:

Criteria: Like [Enter the First Letter of the Applicant's First Name.] & "*"

Which will return all rows where the value in the FirstName begins with the
letter entered. The asterisk wildcard character when used with the Like
operator represents any number of any characters, so if the user enters K for
instance names such as Ken, Kimberley, Keith, Kate, Kelly, Konrad, Kurt etc
will be returned.

A far better solution would be to create an unbound search form with a combo
box set up as follows:

RowSource: SELECT RecruitingApplicantID, FirstName & " " & LastName FROM
RecruitingApplicants ORDER BY FirstName, LastName;

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

RecruitingApplicantsID is the primary key of the table, e.g. an autonumber.
Do not rely on names as a key as these can legitimately be duplicated.

Include buttons on the form to open a form or report based on the query which
should now include the RecruitingApplicantID column whose criterion should be
a parameter which references the combo box on the form, e.g.

Forms![frmSearchApplicants]![cboApplicant]

Remove the criterion from the FirstName column.

You can then select an applicant from the combo box in the form. Provided
the combo box's AutoExpand property is True (Yes), if you type a first name
into the combo box it will progressively go to the first nearest match as
each character is typed in. Once you've selected an applicant and clicked
one of the buttons the form or report will open filtered to that applicant.

Ken Sheridan
Stafford, England

PlarfySoober wrote:
>I have a query which is basically an entire table with a calculated field.
>The calculated field is "FirstName" + " " + "LastName", which are two fields
>of the underlying table.
>
>I wanted to search for a certain record, and the table isn't too large, so
>tried it the simple way (this is in the Query Design Mode):
>
>Field: FirstName
>Table: RecruitingApplicants
>Sort: Ascending
>Show: Yes
>Criteria: >=[Enter the First Letter of the Applicant's First Name.]
>
>This works just fine when opening the query. But using a report OR a form
>which is dependent on the query, it works sometimes, sometimes not, which I
>find odd.
>
>How can I get control of this? My table is getting larger and I'm scrolling
>through quite a few records now.
>
>Thanks.
>
>Don.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

From: PlarfySoober on 15 May 2010 17:06
KenSheridan,

Apologies for the lateness of this reply. And the answer was so simple and
understandable. Thank you very much.

Your second answer, though, I'm going to have to give some time to do. Which
I'm here on a Saturday to do.

I'll keep you posted.

Don.

"KenSheridan via AccessMonster.com" wrote:

> The >= than operation will return names where the name sorts alphabetically
> at or after the letter entered. I think what you are probably aiming for is:
>
> Criteria: Like [Enter the First Letter of the Applicant's First Name.] & "*"
>
> Which will return all rows where the value in the FirstName begins with the
> letter entered. The asterisk wildcard character when used with the Like
> operator represents any number of any characters, so if the user enters K for
> instance names such as Ken, Kimberley, Keith, Kate, Kelly, Konrad, Kurt etc
> will be returned.
>
> A far better solution would be to create an unbound search form with a combo
> box set up as follows:
>
> RowSource: SELECT RecruitingApplicantID, FirstName & " " & LastName FROM
> RecruitingApplicants ORDER BY FirstName, LastName;
>
> BoundColumn: 1
> ColumnCount: 2
> ColumnWidths: 0cm;8cm
>
> If your units of measurement are imperial rather than metric Access will
> automatically convert the last one. The important thing is that the first
> dimension is zero to hide the first column and that the second is at least as
> wide as the combo box.
>
> RecruitingApplicantsID is the primary key of the table, e.g. an autonumber.
> Do not rely on names as a key as these can legitimately be duplicated.
>
> Include buttons on the form to open a form or report based on the query which
> should now include the RecruitingApplicantID column whose criterion should be
> a parameter which references the combo box on the form, e.g.
>
> Forms![frmSearchApplicants]![cboApplicant]
>
> Remove the criterion from the FirstName column.
>
> You can then select an applicant from the combo box in the form. Provided
> the combo box's AutoExpand property is True (Yes), if you type a first name
> into the combo box it will progressively go to the first nearest match as
> each character is typed in. Once you've selected an applicant and clicked
> one of the buttons the form or report will open filtered to that applicant.
>
> Ken Sheridan
> Stafford, England
>
> PlarfySoober wrote:
> >I have a query which is basically an entire table with a calculated field.
> >The calculated field is "FirstName" + " " + "LastName", which are two fields
> >of the underlying table.
> >
> >I wanted to search for a certain record, and the table isn't too large, so
> >tried it the simple way (this is in the Query Design Mode):
> >
> >Field: FirstName
> >Table: RecruitingApplicants
> >Sort: Ascending
> >Show: Yes
> >Criteria: >=[Enter the First Letter of the Applicant's First Name.]
> >
> >This works just fine when opening the query. But using a report OR a form
> >which is dependent on the query, it works sometimes, sometimes not, which I
> >find odd.
> >
> >How can I get control of this? My table is getting larger and I'm scrolling
> >through quite a few records now.
> >
> >Thanks.
> >
> >Don.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1
>
> .
>
 | 
Pages: 1
Prev: Access Query Duplicate records
Next: Group Control Missing