ListBox Problem

[g diddy]

I have a similar problem and I have been following the advice but to no avail. I am a novice with VBA so help would be greatly appreciated. This is my first post so I apologise if it is in the wrong place or format.

In my query I have:

Field: Invigilator no
Table: VerifiedPayments
Criteria: [Forms]![SelectWeek]![List0]

and in the form I have:

     Dim stDocName As String
     Dim varX As Variant
     Dim strWhere As String

    Select Case Me!optOpenReport
    Case 1
    stDocName = "InvigilatorClaims"
    DoCmd.OpenReport stDocName, acPreview

    Case 2
    stDocName = "InvigilatorClaimsIndividual"
    With Me.List0
    For Each varX In .ItemsSelected
         strWhere = strWhere & "," & .ItemData(varX)
    Next varX
    strWhere = "[Invigilator no] In(" & Mid(strWhere, 2) & ")"
    Call DoCmd.OpenReport(ReportName:=stDocName, _
                          View:=acPreview, _
                          WhereCondition:=strWhere)
    End With
           
    End Select

Case 1 is fine. The problem is with Case 2. I am trying to select multiple values from a list box to use for the parameter mentioned above in the query to produce a report. However I keep getting the report with errors (meaning that the invigilator has not been found). (The cases are there because radio buttons are used to produce either one report or the other - I have had it working before with just single values).

[ChipR]

My first step would be to stop the code before line 17 (debug or MsgBox) and take a look at what is in strWhere just before you try to use it.

[g diddy]

My first step would be to stop the code before line 17 (debug or MsgBox) and take a look at what is in strWhere just before you try to use it.

sorry to sound like a noob but how do you do that? (I'm using Microsoft Visual Basic.)

[ChipR]

You can just MsgBox the contents of the variable and see what part is malformed.

'After this:
strWhere = ...

MsgBox strWhere

'Before this:
DoCmd.OpenReport ...

There is also an article on debugging you may want to look at.

[g diddy]

Thank you for the MsgBox function that helped me see what the code is actually doing!

When i selected 2 from the list it came up with:

[Invigilator no] In(1458,221)

which means that it is not enclosing the values in ""
I think!!

Can you see, from this, what is wrong with my code?

Eternally grateful!

[ChipR]

I'm not sure if that's the proper syntax for IN, as I've only seen it used with a subquery SELECT. I'll try to check it out sometime today, but maybe do some experimenting and see if you can get anything to work.

[NeoPa]

This is my first post so I apologise if it is in the wrong place or format.

You're excused G_Diddy, but for reference please avoid posting your questions in existing threads in future. This (thread hijacking) is covered in our Help section - accessible from all pages.

For reference, this thread was split from (and may refer to) List Box and Parameters.

Welcome to Bytes!

[ChipR]

I couldn't get IN to work that way. Here's what you can do instead:

  Dim count As Integer
  ...
  count = 0
  With Me.List0 
    For Each varX In .ItemsSelected 
      If count > 0 Then strWhere = strWhere & " OR "
      strWhere = strWhere & "[Invigilator no] = " & .ItemData(varX) 
      count = count + 1
    Next varX 
  End With 

  DoCmd.OpenReport ...

It would be more efficient to paste the OR on the end every time through the loop, then remove it at the end using Left() or something, but this will do as an example.

[NeoPa]

I'm not sure if that's the proper syntax for IN

It is indeed. I don't believe the automatic adding of quotes works within such a function though.

You (The OP) need to determine the type of the field you are comparing and ensure string or date literals are enclosed by the requisite characters (Quotes (') and Double-Quotes (") - Where and When to use them).

[g diddy]

I tried your way ChipR and received the following when I used the MSGBOX function:

[Invigilator no] = 1458 OR [Invigilator no] = 221 OR [Invigilator no] = 369

I modified the code to:

    Dim stDocName As String
    Dim varX As Variant
    Dim strWhere As String
    Dim count As Integer
    
    Select Case Me!optOpenReport
    Case 1
    stDocName = "InvigilatorClaims"
    DoCmd.OpenReport stDocName, acPreview

    Case 2
    stDocName = "InvigilatorClaimsIndividual"
    count = 0
    With Me.Combo46
    For Each varX In .ItemsSelected
      If count > 0 Then strWhere = strWhere & " OR "
      strWhere = strWhere & "[Invigilator no] = " & "'" & .ItemData(varX) & "'"
      count = count + 1
    Next varX
    
    
    MsgBox strWhere
    
    Call DoCmd.OpenReport(ReportName:=stDocName, _
                          View:=acPreview, _
                          WhereCondition:=strWhere)

    
    End With
        
End Select

and this gave me:

[Invigilator no] = '1458' OR [Invigilator no] = '221' OR [Invigilator no] = '369'

However I now get an error saying the expression is typed incorrectly or is too complex to be evaluated.

I have also been playing round with my original code and managed to get strWhere to contain:

[Invigilator no] IN('1458','1492','221','369',')

However the last one still has ,' after it which isn't right. I have tried so many combinations but cannot get it to work. I know that the ,' is appearing because of the loop. However without adding it in the loop it isn't added at all.

The code so far is:

    Dim stDocName As String
    Dim varX As Variant
    Dim strWhere As String

    Select Case Me!optOpenReport
    Case 1
    stDocName = "InvigilatorClaims"
    DoCmd.OpenReport stDocName, acPreview

    Case 2
    stDocName = "InvigilatorClaimsIndividual"
    With Me.Combo46
    For Each varX In .ItemsSelected
         strWhere = strWhere & "'" & .ItemData(varX) & "'"
         strWhere = strWhere & ","
    Next varX
    strWhere = "[Invigilator no] In('" & Mid(strWhere, 2) & "')"
    
    MsgBox strWhere
    
    Call DoCmd.OpenReport(ReportName:=stDocName, _
                          View:=acPreview, _
                          WhereCondition:=strWhere)
    End With
    
        
End Select

Can you see where i'm going wrong at all? I am such a novice with VBA so I apologise for being a pain!!

[ChipR]

If my code didn't work, then you've got some other issue.
If you still want to do it that way though, use Mid() to remove the last ,'

Mid(strWhere, 1, Len(strWhere)-2)

[g diddy]

Where abouts in the code would that go mate?

[ChipR]

On line 17, just replace your Mid() with the one I wrote. But looking more closely, I think change it to
Mid(strWhere, 1, Len(strWhere)-1)

So is the [Invigilator no] a number field or a text field?

[g diddy]

On line 17, just replace your Mid() with the one I wrote. But looking more closely, I think change it to
Mid(strWhere, 1, Len(strWhere)-1)

So is the [Invigilator no] a number field or a text field?

its a number field mate with the criteria:

[Forms]![SelectWeek]![Combo46]

(n.b. combo46 is just the name, the multi select box is actually a list box i just didn't get round to renaming it in the form)

Just added your code in and now get the message box saying strWhere contains:

[Invigilator no] IN('1458','1492','221','369')

but then an error message saying that the expression is too complex etc as before. Without the ' ' around the numbers I get no error message but my report shows up with errors (i.e. invigilator not found). For a number field that is being passed the parameters like 1458, 1492 etc do they have to be enclosed in ' ' or not?

[ChipR]

Number fields do not need to be enclosed in quotes, so I would expect the first MsgBox output you listed in post #10 to work. We'll have to look into this error about the expression being too complex. What is the exact text of the error message, and does it have an error number?
I still can't get IN to work that way, but since it's the same error, I would guess that it's the same solution.

I just noticed you have an extra quote in your string.

strWhere = "[Invigilator no] In('" & Mid(strWhere, 2) & "')"

Since you are already putting quotes around each number, change this to:

strWhere = "[Invigilator no] In(" & Mid(strWhere, 1, Len(strWhere)-1) & ")"