Search more then one field at once

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hiya,

A database I was just about finished with had to undergo a change as
requested by the main person who will be using it. Before meeting with him,
he could get a report based on the Item # of the product he wanted, which was
very easy to impliment. When I met with him, he requested to add three more
fields for Item #, and here is where I'm stumped on the search.

So first, I was reading some of the threads I searched for, and saw that
some say that similar data, such as four fields of Item #'s (maybe?),
shouldn't be in multiple fields. Is there a way to pull these four into one,
or should I?

Is there a way to tell my query: If any of these four fields match the user
input of Item #, display the record? (if appropriate with the previous
paragraph)

Thank you in advance for your time and effort! :)
Stephanie
 
If there could be multiple items, you do need to create a related table so
that the items go there. This table relates to the first one so that one
main record can have many related items.

For an example, open the Northwind sample database that installed with
Access. Open the Relationships window. Look at the Orders table and the
Order Details table. One order can have multiple items (products) ordered,
so the items are in the related table. If you can't find Northwind, there is
a screenshot in this article:
http://allenbrowne.com/AppRelReport.html

It is actually possible to apply the criteria against multiple fields. The
example below shows how to build such a filter for a form.

Dim strDelim As String
Dim strWhere As String
strDelim = """" 'Omit this line if item1 is a Number field.
With Me.txtSearchBox
If Not IsNull(.Value) Then
strWhere = "([item1] = " & strDelim & .Value & strDelim & _
") OR ([item2] = " & strDelim & .Value & strDelim & _
") OR ([item3] = " & strDelim & .Value & strDelim & _
") OR ([item4] = " & strDelim & .Value & strDelim & & ")"

Me.Filter = strWhere
Me.FilterOn = True
End If
End With

But the relational design is the better approach if you can still do it at
this stage.
 
Hi Allen,

Once again, thank you for replying!

I will see what I can do about the relationships, and have a decent idea
based on what I saw at your link (You have a very helpful site). This may
sound basic, but I'm not sure where to insert the code that you gave me in
the message I'm responding to.

As a note, I do plan to get myself a decent book on Access so I can stop
asking so many questions. I have, however, learned quite a bit more then I
thought I would have.

Thank you,
Stephanie

Allen Browne said:
If there could be multiple items, you do need to create a related table so
that the items go there. This table relates to the first one so that one
main record can have many related items.

For an example, open the Northwind sample database that installed with
Access. Open the Relationships window. Look at the Orders table and the
Order Details table. One order can have multiple items (products) ordered,
so the items are in the related table. If you can't find Northwind, there is
a screenshot in this article:
http://allenbrowne.com/AppRelReport.html

It is actually possible to apply the criteria against multiple fields. The
example below shows how to build such a filter for a form.

Dim strDelim As String
Dim strWhere As String
strDelim = """" 'Omit this line if item1 is a Number field.
With Me.txtSearchBox
If Not IsNull(.Value) Then
strWhere = "([item1] = " & strDelim & .Value & strDelim & _
") OR ([item2] = " & strDelim & .Value & strDelim & _
") OR ([item3] = " & strDelim & .Value & strDelim & _
") OR ([item4] = " & strDelim & .Value & strDelim & & ")"

Me.Filter = strWhere
Me.FilterOn = True
End If
End With

But the relational design is the better approach if you can still do it at
this stage.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Luna Saisho said:
A database I was just about finished with had to undergo a change as
requested by the main person who will be using it. Before meeting with
him,
he could get a report based on the Item # of the product he wanted, which
was
very easy to impliment. When I met with him, he requested to add three
more
fields for Item #, and here is where I'm stumped on the search.

So first, I was reading some of the threads I searched for, and saw that
some say that similar data, such as four fields of Item #'s (maybe?),
shouldn't be in multiple fields. Is there a way to pull these four into
one,
or should I?

Is there a way to tell my query: If any of these four fields match the
user
input of Item #, display the record? (if appropriate with the previous
paragraph)

Thank you in advance for your time and effort! :)
Stephanie
Click to expand...
Click to expand...
 
1. Add a command button to execute the search,

2. Set the On Click property of the command button to:
[Event Procedure]

3. Click the Build button (...) beside that property.
Access opens a code window.

4. Paste the code in there in between the lines:
Private Sub ...
and
End Sub

5. Replace:
Me.txtSearchBox
with the name of the text box you have.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Luna Saisho said:
Hi Allen,

Once again, thank you for replying!

I will see what I can do about the relationships, and have a decent idea
based on what I saw at your link (You have a very helpful site). This may
sound basic, but I'm not sure where to insert the code that you gave me in
the message I'm responding to.

As a note, I do plan to get myself a decent book on Access so I can stop
asking so many questions. I have, however, learned quite a bit more then
I
thought I would have.

Thank you,
Stephanie

Allen Browne said:
If there could be multiple items, you do need to create a related table
so
that the items go there. This table relates to the first one so that one
main record can have many related items.

For an example, open the Northwind sample database that installed with
Access. Open the Relationships window. Look at the Orders table and the
Order Details table. One order can have multiple items (products)
ordered,
so the items are in the related table. If you can't find Northwind, there
is
a screenshot in this article:
http://allenbrowne.com/AppRelReport.html

It is actually possible to apply the criteria against multiple fields.
The
example below shows how to build such a filter for a form.

Dim strDelim As String
Dim strWhere As String
strDelim = """" 'Omit this line if item1 is a Number field.
With Me.txtSearchBox
If Not IsNull(.Value) Then
strWhere = "([item1] = " & strDelim & .Value & strDelim & _
") OR ([item2] = " & strDelim & .Value & strDelim & _
") OR ([item3] = " & strDelim & .Value & strDelim & _
") OR ([item4] = " & strDelim & .Value & strDelim & & ")"

Me.Filter = strWhere
Me.FilterOn = True
End If
End With

But the relational design is the better approach if you can still do it
at
this stage.

Luna Saisho said:
A database I was just about finished with had to undergo a change as
requested by the main person who will be using it. Before meeting with
him,
he could get a report based on the Item # of the product he wanted,
which
was
very easy to impliment. When I met with him, he requested to add three
more
fields for Item #, and here is where I'm stumped on the search.

So first, I was reading some of the threads I searched for, and saw
that
some say that similar data, such as four fields of Item #'s (maybe?),
shouldn't be in multiple fields. Is there a way to pull these four
into
one,
or should I?

Is there a way to tell my query: If any of these four fields match the
user
input of Item #, display the record? (if appropriate with the previous
paragraph)

Thank you in advance for your time and effort! :)
Stephanie
Click to expand...
Click to expand...
Click to expand...
 
Got it, thank you again! :)

Allen Browne said:
1. Add a command button to execute the search,

2. Set the On Click property of the command button to:
[Event Procedure]

3. Click the Build button (...) beside that property.
Access opens a code window.

4. Paste the code in there in between the lines:
Private Sub ...
and
End Sub

5. Replace:
Me.txtSearchBox
with the name of the text box you have.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Luna Saisho said:
Hi Allen,

Once again, thank you for replying!

I will see what I can do about the relationships, and have a decent idea
based on what I saw at your link (You have a very helpful site). This may
sound basic, but I'm not sure where to insert the code that you gave me in
the message I'm responding to.

As a note, I do plan to get myself a decent book on Access so I can stop
asking so many questions. I have, however, learned quite a bit more then
I
thought I would have.

Thank you,
Stephanie

Allen Browne said:
If there could be multiple items, you do need to create a related table
so
that the items go there. This table relates to the first one so that one
main record can have many related items.

For an example, open the Northwind sample database that installed with
Access. Open the Relationships window. Look at the Orders table and the
Order Details table. One order can have multiple items (products)
ordered,
so the items are in the related table. If you can't find Northwind, there
is
a screenshot in this article:
http://allenbrowne.com/AppRelReport.html

It is actually possible to apply the criteria against multiple fields.
The
example below shows how to build such a filter for a form.

Dim strDelim As String
Dim strWhere As String
strDelim = """" 'Omit this line if item1 is a Number field.
With Me.txtSearchBox
If Not IsNull(.Value) Then
strWhere = "([item1] = " & strDelim & .Value & strDelim & _
") OR ([item2] = " & strDelim & .Value & strDelim & _
") OR ([item3] = " & strDelim & .Value & strDelim & _
") OR ([item4] = " & strDelim & .Value & strDelim & & ")"

Me.Filter = strWhere
Me.FilterOn = True
End If
End With

But the relational design is the better approach if you can still do it
at
this stage.


A database I was just about finished with had to undergo a change as
requested by the main person who will be using it. Before meeting with
him,
he could get a report based on the Item # of the product he wanted,
which
was
very easy to impliment. When I met with him, he requested to add three
more
fields for Item #, and here is where I'm stumped on the search.

So first, I was reading some of the threads I searched for, and saw
that
some say that similar data, such as four fields of Item #'s (maybe?),
shouldn't be in multiple fields. Is there a way to pull these four
into
one,
or should I?

Is there a way to tell my query: If any of these four fields match the
user
input of Item #, display the record? (if appropriate with the previous
paragraph)

Thank you in advance for your time and effort! :)
Stephanie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top