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]

@ChipR
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]

@g diddy
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]

@ChipR
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]

@ChipR
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) & ")"

[g diddy]

The error says:

This expression is typed incorrectly,or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
 

This error doesn't show when the quotes are not around the numbers though. I'm going to leave them off from now on (thanks for letting me know that by the way...i would have been forever trying to correctly place these quotes when they are not needed!!!). The only problem now is that the report still shows errors so the multiselect box must not be passing the values to the parameter correctly. I'm really not sure why. It used to work with single values, I would select the invigilators name (e.g. Anderson, David) and it would pass their id (1458) to the query which then created the report with the results on. The only thing I have done differently is change it from a combo box to a multi select (simple) list box. Would that have changed anything at all? The row source is still the same too

(n.b. when 4 were selected strWhere contained:

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

which looks like it is in the correct format...well to me anyway but i'm a novice!!)

[g diddy]

I've found this webpage which seems really relevant but I can't make head nor tail of it. You might find it useful though

http://www.fontstuff.com/access/acctut18.htm

[ChipR]

Thanks for the link. According to that, the strWhere you have looks fine. What errors do you get on the report? What do you mean by "pass their id to the query"?

[g diddy]

it just says #error which is because the invigilator no has not been recognised.
in the query it has for the field Invigilator no the criteria:

[Forms]![SelectWeek]![Combo46]

which takes the value from the multi select list box for use as a parameter

so when the user selects an invigilator from the multi select list box (named Combo46) it should pass their ID to the query which is then used to produce the report. When the list box only allowed the user to select single values it worked correctly it is only since trying to change it to multiple select that it failed. I hope that helps.

[ChipR]

You may not be able to reference the actual value selected in a multi-select list box that way. Notice in the code you have to reference the Combo46.ItemsSelected collection. What you could do is whenever an item is selected in the list box, use code to generate the string in another (hidden) text box, and then reference that text box in your query.

[g diddy]

that sounds like a plan! how would I go about doing this though? I know how to create text boxes but I'm not entirely sure on how to generate the string for use as a reference
(eternally grateful!!)

[ChipR]

Are you using that reference in the query to filter the query results, then using the strWhere to do the exact same thing? If that is the case, you just remove that condition in the query, let it return all the records, and use the where condition in the DoCmd.OpenReport to see only the ones you want.

[g diddy]

YES!! that was the problem all along!! Thank you so much for all your help mate. I really appreciate it thank you!!

[ChipR]

Glad to hear you got it working. I learned a little something too.

[NeoPa]

Just to clarify for anyone reading who gets confused by all the to-ing & fro-ing :

The best way to apply filtering to a report is to design a query (or Record Source) which returns all records, then apply the filter to the report itself. This is most easily done in the call that opens the report itself using the WhereCondition parameter.

It's possible to apply criteria to the underlying query (as the OP did in this case) only to find that flexibility later is severely reduced.

BTW Sorry that I didn't help in time guys. By the time I had caught up with the problem (Post #14 gave the clue - but wasn't specific about what type of Criteria it was working with so I missed it. It was only at post #19 I realised the OP was trying to applying the criteria within the query) Chip had already supplied the solution.

As the query was already saying "WHERE [Invigilator] = [Value from form]" the use of IN() could never work (Equivalent to [Invigilator = IN(...)). Only one equality operator can be used at a time. Either "=" OR "IN()" but not both.