Wherecondition troubles with multiple entries

[Ares6881]

Hi everyone, I'm new to these forums, but I'm having a bit of an issue. I'm basically trying to get the coding right on the wherecondition, but can't seem to figure out the correct formatting for using multiple entries. This is being used to make a report that pulls selected addresses by name. List2 is a List box, and the For loop creates data that looks like this: "Bill" OR "Jim" OR "Sandy"

Anyway, here's the code.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
    
    Dim x As Integer
    Dim y As String
    Dim z As String
    Dim stDocName As String
    
    z = ""
    For x = 0 To List2.ListCount - 1
        y = List2.Column(0, x)
        y = """" & y & """"
        If x = 0 Then
            z = y
        Else
            z = z & " OR " & y
        End If
    Next x
    stDocName = "rptAddresses"
    DoCmd.OpenReport stDocName, acPreview, , "ccustno='" & z & "'"

Exit_Command11_Click:
    Exit Sub

Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
    
End Sub

[ADezii]

Hi everyone, I'm new to these forums, but I'm having a bit of an issue. I'm basically trying to get the coding right on the wherecondition, but can't seem to figure out the correct formatting for using multiple entries. This is being used to make a report that pulls selected addresses by name. List2 is a List box, and the For loop creates data that looks like this: "Bill" OR "Jim" OR "Sandy"

Anyway, here's the code.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
    
    Dim x As Integer
    Dim y As String
    Dim z As String
    Dim stDocName As String
    
    z = ""
    For x = 0 To List2.ListCount - 1
        y = List2.Column(0, x)
        y = """" & y & """"
        If x = 0 Then
            z = y
        Else
            z = z & " OR " & y
        End If
    Next x
    stDocName = "rptAddresses"
    DoCmd.OpenReport stDocName, acPreview, , "ccustno='" & z & "'"

Exit_Command11_Click:
    Exit Sub

Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
    
End Sub

Here is 1 Not-So-Obvious solution run against my Test Data. It has been debugged and is fully functional. Copy and Paste the code, make your necessary substitutions, and you should be ready to go. Let me know how you make out.
[CODE=sql]Dim x As Integer
Dim y As String
Dim z As String
Dim stDocName As String

stDocName = "rptEmploye es"

z = ""
For x = 0 To List2.ListCount - 1
y = "[LastName]='" & List2.Column(0, x) & "'"
If x = 0 Then
z = y
Else
z = z & " Or " & y
End If
Next x

DoCmd.OpenRepor t stDocName, acViewPreview, , z[/CODE]

[Rabbit]

You can't do:
[Code=sql]
SomeField = "Value1" OR "Value2"
[/Code]

You have two options:
[Code=sql]
SomeField = "Value1" OR SomeField = "Value2"
SomeField In ("Value1", "Value2")
[/Code]

[ADezii]

You can't do:
[Code=sql]
SomeField = "Value1" OR "Value2"
[/Code]

You have two options:
[Code=sql]
SomeField = "Value1" OR SomeField = "Value2"
SomeField In ("Value1", "Value2")
[/Code]

Hello Rabbit:
I don't think the IN approach will work within the current context. It interprets the individual items within the IN construct as additional, and illegal, Arguments to the OpenReport() Method. This was my initial approach to the problem but didn't seem to be workable because of the stated reason. Just some useless information I figured you may possibly want to know. See ya later!

[MikeTheBike]

Hi

    
z = ""
    For x = 0 To List2.ListCount - 1
        y = List2.Column(0, x)
        y = """" & y & """"
        If x = 0 Then
            z = y
        Else
            z = z & " OR " & y
        End If
    Next x
    stDocName = "rptAddresses"
    DoCmd.OpenReport stDocName, acPreview, , "ccustno='" & z & "'"

[MikeTheBike]

Hi

I think this may be a little simpler.

        z = "ccustno = '"  & List2.Column(0, 0) & "'"
    For x = 1 To List2.ListCount - 1
        z = z & " OR ccustno = '"  & List2.Column(0, x) & "'"
    Next x
    stDocName = "rptAddresses"
    DoCmd.OpenReport stDocName, acPreview, , z

This assumes there is at least one item in the list, and if not you would need to test for the condition anyway.

MTB

[Rabbit]

Hello Rabbit:
I don't think the IN approach will work within the current context. It interprets the individual items within the IN construct as additional, and illegal, Arguments to the OpenReport() Method. This was my initial approach to the problem but didn't seem to be workable because of the stated reason. Just some useless information I figured you may possibly want to know. See ya later!

I just tried this and this works:

[Code=vb]
Private Sub Command0_Click( )
Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "UserID In (""Andy"", ""Bill"")"
stDocName = "Form1"

DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
[/Code]

[ADezii]

I just tried this and this works:

[Code=vb]
Private Sub Command0_Click( )
Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "UserID In (""Andy"", ""Bill"")"
stDocName = "Form1"

DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
[/Code]

Thanks for the info, Rabbit. I really wasn't kidding when I said that I was giving you some useless information. (LOL).

[Ares6881]

Thank you everyone for all your responses, I was able to get it working with Mike's simplified version. I've bookmarked this site and plan to return if I run into any more snags, thanks!

[Rabbit]

Thank you everyone for all your responses, I was able to get it working with Mike's simplified version. I've bookmarked this site and plan to return if I run into any more snags, thanks!

Technically mines is simpler but no hard feelings. lol.

Anyways, glad you got it working.