A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Searching for Records in a Report



 
 
Thread Tools Display Modes
  #1  
Old May 3rd, 2010, 10:06 PM posted to microsoft.public.access.gettingstarted
PlarfySoober
external usenet poster
 
Posts: 68
Default Searching for Records in a Report

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.



  #2  
Old May 3rd, 2010, 11:21 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Searching for Records in a Report

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/For...arted/201005/1

  #3  
Old May 15th, 2010, 10:06 PM posted to microsoft.public.access.gettingstarted
PlarfySoober
external usenet poster
 
Posts: 68
Default Searching for Records in a Report

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/For...arted/201005/1

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:45 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.