Multiple Criteria

  • Thread starter Thread starter Gee
  • Start date Start date
G

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
 
Gee said:
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.
Click to expand...


SELECT table.*
FROM table
WHERE Set1=Forms!theform.listbox
OR Set2=Forms!theform.listbox
OR Set3=Forms!theform.listbox
 
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".
 
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
 
Gee said:
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.
Click to expand...


It is almost certain that you have not normalized your table correctly
 
stLinkCriteria = "[Set1]=" & "'" & Me![List60] & "'" & _
" OR [Set2]=" & "'" & Me![List60] & "'" & _
" OR [Set3]=" & "'" & Me![List60] & "'"

--

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


Gee said:
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

Gee said:
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".
Click to expand...
Click to expand...
 
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.
 
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
 
Back
Top