Is it possible....

Hello,
I need to know, is it possible to do a find or a search in
multiple fields in a table. I've got 4 fields of serial
numbers in my table and I am having to search each field
one at a time. Can I do a search by just highlighting all
4 fields and do a find that way?
Can I create a customized search using a command button to
search all 4 fields?

Thanks
Dan Myers
Transource Computers

On Mon, 26 Jul 2004 16:40:25 -0700, "Dan Myers"
wrote:

Hello,
I need to know, is it possible to do a find or a search in
multiple fields in a table. I've got 4 fields of serial
numbers in my table and I am having to search each field
one at a time. Can I do a search by just highlighting all
4 fields and do a find that way?
Can I create a customized search using a command button to
search all 4 fields?

Two answers:

Yes, you can search DOZENS of fields. In the query grid, you can put a
criterion

[Enter serial number:]

under each of the four serial number fields; put the criterion on a
new line under each field, and Access will use "OR" logic, returning a
record if the serial number entered by the user in response to the
"Enter serial number:" prompt is found in any one of the fields.

Deeper answer:

CONSIDER CHANGING YOUR TABLE STRUCTURE. If you have four serial
numbers per record, you are "committing spreadsheet upon a database" -
a venial sin, with penance consisting of a requirement to read some
good publication on relational theory. g Embedding a one to many
relationship within a record IS BAD DESIGN; you would be wise to split
this table into two tables in a one to many relationship, so each
record in this table can be related to zero, one, four... or even five
or six... serial numbers. With this "normalized" design, you can
create a Query joining the two tables and search the single serial
number field.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Create a command button on your form and add this code:


Private Sub cmdFindRecord_Click()
On Error GoTo Err_cmdFindRecord_Click

' %e = "Search Only Current Field" set to no ; %ha = "Any
Part of Field" ; %n = Set Cusor at first field
' %l{END} = "Changes for current Field to current
Database"
SendKeys "%e%l{END}%ha%n", False


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, ,
acMenuVer70

Exit_cmdFindRecord_Click:
Exit Sub

Err_cmdFindRecord_Click:
MsgBox Err.Description
Resume Exit_cmdFindRecord_Click

End Sub


Hope this helps
Watch for word wrap in the above code!

RK






-----Original Message-----
On Mon, 26 Jul 2004 16:40:25 -0700, "Dan Myers"

wrote:

Hello,
I need to know, is it possible to do a find or a search
in
multiple fields in a table. I've got 4 fields of serial
numbers in my table and I am having to search each
field
one at a time. Can I do a search by just highlighting
all
4 fields and do a find that way?
Can I create a customized search using a command button
to
search all 4 fields?

Two answers:

Yes, you can search DOZENS of fields. In the query grid,
you can put a
criterion

[Enter serial number:]

under each of the four serial number fields; put the
criterion on a
new line under each field, and Access will use "OR"
logic, returning a
record if the serial number entered by the user in
response to the
"Enter serial number:" prompt is found in any one of the
fields.

Deeper answer:

CONSIDER CHANGING YOUR TABLE STRUCTURE. If you have four
serial
numbers per record, you are "committing spreadsheet upon
a database" -
a venial sin, with penance consisting of a requirement
to read some
good publication on relational theory. g Embedding a
one to many
relationship within a record IS BAD DESIGN; you would be
wise to split
this table into two tables in a one to many
relationship, so each
record in this table can be related to zero, one,
four... or even five
or six... serial numbers. With this "normalized" design,
you can
create a Query joining the two tables and search the
single serial
number field.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
.