Concatenating List box to a Text Pox

[joeserrone]

Hello,
I created a code that when pressing a button everything that is selected in a list box is placed in a text box. But I would like it with the format of "..." or "..." or "..." the current code I wrote doesn't place the " at the beginning and end.
Private Sub Command18_Click()
Dim txtValue As String
Dim varItem As Variant
Dim strlnameselect As String
'Cycle through selected rows in listbox

For Each varItem In Me.Queueselect.ItemsSelected
Select Case Len(txtValue)
Case 0
txtValue = Me.Queueselect.ItemData(varItem)
Case Else
txtValue = txtValue & Chr(34) & " Or " & Chr(34) & Me.Queueselect.ItemData(varItem)
End Select
Next
'Assign variable value to textbox
Me.Queuetorun.Value = txtValue

End Sub

The result is:

04" Or "06" Or "11" Or "18

I need it to show "04" Or "06" Or "11" Or "18"

 

[Mark Rock]

Your original code:

Private Sub Command18_Click()
Dim txtValue As String
Dim varItem As Variant
Dim strlnameselect As String
'Cycle through selected rows in listbox

For Each varItem In Me.Queueselect.ItemsSelected
Select Case Len(txtValue)
Case 0
txtValue = Me.Queueselect.ItemData(varItem)
Case Else
txtValue = txtValue & Chr(34) & " Or " & Chr(34) & Me.Queueselect.ItemData(varItem)
End Select
Next
'Assign variable value to textbox
Me.Queuetorun.Value = txtValue

End Sub

Suggested modification:

Private Sub Command18_Click()
Dim txtValue As String
Dim varItem As Variant
Dim strlnameselect As String
'Cycle through selected rows in listbox

For Each varItem In Me.Queueselect.ItemsSelected
Select Case Len(txtValue)
Case 0
txtValue = [U]" &[/U] Me.Queueselect.ItemData(varItem)
Case Else
txtValue = txtValue & Chr(34) & " Or " & Chr(34) & Me.Queueselect.ItemData(varItem)
End Select
Next
'Assign variable value to textbox
Me.Queuetorun.Value = txtValue[U] & "[/U]

End Sub

That do what you want?

 

[boblarson]

Change to:

Dim txtValue As String
Dim varItem As Variant
Dim strlnameselect As String
Dim intCount as Integer
'Cycle through selected rows in listbox

For Each varItem In Me.Queueselect.ItemsSelected
   intCount = intCount + 1
Select Case Len(txtValue)
Case 0
   txtValue = Chr(34) & Me.Queueselect.ItemData(varItem)
Case Else
   txtValue = txtValue & Chr(34) & " Or " & Chr(34) & Me.Queueselect.ItemData(varItem)
End Select
   If intCount=Me.Queueselect.ItemsSelected.Count Then
      txtValue = txtValue & Chr(34)
   End If
Next
'Assign variable value to textbox
Me.Queuetorun.Value = txtValue

End Sub

 

[boblarson]

Either Mark's or my way should work for you. Mark's is less coding, but you will have to use Chr(34) instead of what he wrote.

 

[Mark Rock]

Sorry about the typos. Let's pretend I didn't actually suggest a single quote instead of Chr(34)s, o'kay?

I was really shooting for something like """" instead of chr(34), but decided to change at last minute.

 

[boblarson]

Mark - Not a problem, good work in putting together the solution, it just needed a little tweaking, just like me I was over thinking the process. So we both got something out of it.

 

[joeserrone]

Thanks for your help but I did have another question.

now that my text box looks good "A" or "B" or "C"

I tried linking this to the criteria section of my query but is not reading it. I get no results even if the format is fine.

Any ideas how I can get the query to read the texbox and give me results based on this

 

[boblarson]

Let me ask - Does the text in the field you are wanting results from actually contain ONLY
a letter A
or a letter B
or a letter C?
Nothing else?

 

[joeserrone]

No letters but it will contain the pockets where the work is flowing into similar to this:
02
03
04
05
06
07
08
09
10
11
etc...

I need to have the query look at the text box and return the results based on the criteria

 

[boblarson]

So you've tested with "02" OR "03" etc. Also, that field IS text isn't it? If not, then it's not going to work with the quotes.

 

[joeserrone]

Yes I've tried by typing "02" or "03" in the Query Criteria and get the wanted results but If I create an expression to look at the Text field in the form with the code we used above it doesn't generate anything.

 

[boblarson]

Any chance you can post a stripped down version here (just the form, query and table involved)?

 

[pbaldy]

Bob appears to be offline. It should be noted that when you type "02" or "03" into the query builder, it will generate the correct SQL of:

Field = "02" or Field = "03"

When you try to point to a field containing the same thing, you end up with "bad" SQL:

Field = "02" or "03"

When basing a report on a multiselect listbox (if that's what you're doing), I generally build a wherecondition with the multiselect code and use that when opening the report. I recall having trouble building anything that a query could look at directly, like you're trying to do.

edit: oops, Bob came back! Sorry for jumping in on you Bob.

 

[boblarson]

Paul - any help you want to give is good help. No worries!