Multiple Criteria

[Gee]

I have a query fed by a table where:
"Set1" can be A, B, C, D or E.
"Set2" can be A, B, C, D or E.
"Set3" can be A, B, C, D or E.

I have a form with a list box listing A, B, C, D or E.

What I'm trying to do is open a form that lists all the records with A in
Set1, Set2 or Set3....every record with A or B or C or D or E in any one of
the sets...when A, B, C, D or E are selected in the list box.

Say the user selects "B" in the list box, they then click a button which
shows all the records with "B" in any of the sets.

Anyone have an idea how I can do that?

Thank you in advance for any help you can give me.

Gee

 

[Marshall Barton]

I have a query fed by a table where:
"Set1" can be A, B, C, D or E.
"Set2" can be A, B, C, D or E.
"Set3" can be A, B, C, D or E.

I have a form with a list box listing A, B, C, D or E.

What I'm trying to do is open a form that lists all the records with A in
Set1, Set2 or Set3....every record with A or B or C or D or E in any one of
the sets...when A, B, C, D or E are selected in the list box.

Say the user selects "B" in the list box, they then click a button which
shows all the records with "B" in any of the sets.

SELECT table.*
FROM table
WHERE Set1=Forms!theform.listbox
OR Set2=Forms!theform.listbox
OR Set3=Forms!theform.listbox

 

[Gee]

Thanks so much for answering, but could you be a bit more specific? When I
put it in my code it turns read and doesn't like the "OR".

 

[Gee]

This is what I had been trying, but it includes only the ones with both...I
can't figure out how to the the "OR" in there.

Private Sub Command64_Click()
On Error GoTo Err_Command64_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "SearchByRequestor"

stLinkCriteria = "[Set1]=" & "'" & Me![List60] & "'"
stLinkCriteria = "[Set2]=" & "'" & Me![List60] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command64_Click:
Exit Sub

Err_Command64_Click:
MsgBox Err.Description
Resume Exit_Command64_Click

End Sub

 

[Mike Painter]

I have a query fed by a table where:
"Set1" can be A, B, C, D or E.
"Set2" can be A, B, C, D or E.
"Set3" can be A, B, C, D or E.

I have a form with a list box listing A, B, C, D or E.

What I'm trying to do is open a form that lists all the records with
A in Set1, Set2 or Set3....every record with A or B or C or D or E in
any one of the sets...when A, B, C, D or E are selected in the list
box.

Say the user selects "B" in the list box, they then click a button
which shows all the records with "B" in any of the sets.

Anyone have an idea how I can do that?

Thank you in advance for any help you can give me.

It is almost certain that you have not normalized your table correctly

 

[Ken Snell]

stLinkCriteria = "[Set1]=" & "'" & Me![List60] & "'" & _
" OR [Set2]=" & "'" & Me![List60] & "'" & _
" OR [Set3]=" & "'" & Me![List60] & "'"

--

Ken Snell
http://www.accessmvp.com/KDSnell/

This is what I had been trying, but it includes only the ones with
both...I
can't figure out how to the the "OR" in there.

Private Sub Command64_Click()
On Error GoTo Err_Command64_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "SearchByRequestor"

stLinkCriteria = "[Set1]=" & "'" & Me![List60] & "'"
stLinkCriteria = "[Set2]=" & "'" & Me![List60] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command64_Click:
Exit Sub

Err_Command64_Click:
MsgBox Err.Description
Resume Exit_Command64_Click

End Sub

Thanks so much for answering, but could you be a bit more specific? When
I
put it in my code it turns read and doesn't like the "OR".

 

[Marshall Barton]

In VBA you would write it like:

stLinkCriteria = "Set1='" & Me!List60 & _
"' OR Set2='" & Me!List60 & _
"' OR Set3='" & Me!List60 & "' "
' MsgBox stLinkCriteria
DoCmd.OpenForm stDocName, , , stLinkCriteria

Uncomment the MsgBox line to see that the constructed
criteria ends up looking like the SQL Where clause I posted
earlier. Remove the MsgBox line when you are confident
about how all this works.

You should spend a minute giving your controls more
meaningful names that make you code easier to see what it is
doing. List60 just doesn't convey anything about what the
list box is being used for.

I also want to underscore Mike's comment about your table
being un normalized, This is an extremely important concept
in any database and you should take some time out to
understand and practice "Normalization" (Google it) so you
don't have to use kludgy code like the above.

 

[Gee]

Thank you so much! It totally worked and I can go on with my life.
I'll look up the Normalization you guys suggested.
Thanks,
Gee