2 questions on multi select list parameters

[CraigDolphin]

As a suggestion: you could make a copy of the db to share, and remove all real data and repopulate with some fake data enough to demonstrate the issue. Sometimes it's a lot easier to delve in hands-on than play 20 questions

 

[pbaldy]

Add this right before the OpenReport line:

Debug.Print strWhere

which will print the contents of that variable to the VBA Immediate window. If you don't see the problem, post the results of that here along with the data type of [Cost Center].

 

[boblarson]

Change this:

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem

to this:

[COLOR="Red"]strWhere = "[Cost Center] In ("[/COLOR]

For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & "'" & ctl.ItemData(varItem) & [COLOR="red"]"', "[/COLOR]
Next varItem
[COLOR="red"]strWhere = Left(strWhere, Len(strWhere)-2 & ")"[/COLOR]

And then Change this:

DoCmd.OpenReport "rptSummary", acPreview, , "[Cost Center] IN(" & strWhere & ")"

to this:

DoCmd.OpenReport "rptSummary", acPreview, , [COLOR="red"]strWhere[/COLOR]

 

[pofe333]

As a suggestion: you could make a copy of the db to share, and remove all real data and repopulate with some fake data enough to demonstrate the issue. Sometimes it's a lot easier to delve in hands-on than play 20 questions

That's a great idea. I may do that if I can't get this nailed down pretty quickly.

Add this right before the OpenReport line:

Debug.Print strWhere

which will print the contents of that variable to the VBA Immediate window. If you don't see the problem, post the results of that here along with the data type of [Cost Center].

I will try this in a few moments along with Bob's suggestion next.

 

[pofe333]

Change this:

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem

to this:

[COLOR=red]strWhere = "[Cost Center] In ("[/COLOR]
 
For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & "'" & ctl.ItemData(varItem) & [COLOR=red]"', "[/COLOR]
Next varItem
[COLOR=red]strWhere = Left(strWhere, Len(strWhere)-2 & ")"[/COLOR]

And then Change this:

DoCmd.OpenReport "rptSummary", acPreview, , "[Cost Center] IN(" & strWhere & ")"

to this:

DoCmd.OpenReport "rptSummary", acPreview, , [COLOR=red]strWhere[/COLOR]

Bob, I get an error on strWhere = Left(strWhere, Len(strWhere)-2 & ")" above. It says expecting operator in the code builder. If I put a parenthesis on it the error when running the command says mismatch. I'll work on creating a copy of the db with sensitive info removed to post next week.

 

[boblarson]

sorry, forgot a paren:

strWhere = Left(strWhere, Len(strWhere))-2 & ")"

 

[pofe333]

sorry, forgot a paren:

strWhere = Left(strWhere, Len(strWhere))-2 & ")"

I get Runtime Error 13, Type mismatch.

I'm think I'm going to have to post up my db. I've got a buddy who works with code, so I'll see if he might be able to look at it this weekend. All 3 of you have gotten me way beyond where I was and I appreciate it very much! I'll check back after the weekend if I'm still stuck.

 

[boblarson]

Go ahead and post it. If Cost Center is numeric then you would want to leave off the quotes during the loop for the list box.