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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|