using For Each statement

[teel73]

I have a multi-select listbox (lstValues) and I am using the ItemsSelected property to return the values selected in the list in a message box. The items selected in the list are: Apples, Oranges, Grapes. My message box displays it as: or Apples or Oranges or Grapes

I want my message box to display it as follows: "Apples" or "Oranges" or "Grapes" How can I get it to display like that. Below is the code I currently have:

Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim x As Variant

Set frm = Form_Form1

Set ctl = frm!lstValue

For Each varItm In ctl.ItemsSelected
    
    x = x & " Or " & ctl.ItemData(varItm)
    
Next varItm

MsgBox x

 

[Rabbie]

Try this

Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim x As Variant

Set frm = Form_Form1

Set ctl = frm!lstValue

For Each varItm In ctl.ItemsSelected
    
    x = x & " Or """ & ctl.ItemData(varItm) & '"'    
Next varItm

MsgBox x

 

[Banana]

If you need to insert " in a string, you should use chr(34) instead. E.g.

x = x & " Or " & Chr(34) & ctl.ItemData(varItm) & Chr(34) & " "

 

[teel73]

Thanks you all. But I don't need to concactenate the "or" until after the first value. That's the problem I'm having. My code puts the "or" in front of the string. I want it to display the first selection then every other item would have the "or" concactenated.

My code does this: or Apples or Oranges or Grapes.

I want it to do this: Apples or Oranges or Grapes.

I'm not to concerned with the quotations. Any other ideas?

 

[Banana]

For each X in Y
    String = X & "Or" & String
Next X

String = Right(String, Len(String)-3)

Len() function gives you the number of characters in a string. We then subtract 3 so we remove three character "Or " (space included), using Right () (I usually use Left() because my "Or" comes after the string, but I've forgotten how I did it... but whatever. Same result.

 

[teel73]

thank you Banana for your suggestion. I did something similar. I used the Mid function to extract the Or and the space. thanks again.

Now I'm having another problem that is bugging me that I can't figure it out. I've created form filters using vba before but I can't get this simple filter to work. Below is the code:

dim frm as form
set frm = form_frmUpdate

frm.filter = "[fruit] = 'apple' or 'oranges' or 'grapes'"
frm.filterOn = true

When I run that against my form, it returns all records but it shows (filtered) in the navigation bar. And when I substitute "or" for "and" it returns 0 records. What in the crap am I doing wrong? Could you help me or anyone.

Thanks

 

[Banana]

1) Why are we using Mid() function? Do you know what are the differences between Left(), Right() and Mid()?

2) Your filter could be in fact working correctly. If your fruit only has three entries, then this will indeed return all records. Furthermore, using "And" is an impossibility because you're asking a Fruit to be *both* Apple and Orange, which cannot be, so of course you get nothing in return.

 

[teel73]

Maybe my last thread was a bit confusing. I used the Mid function instead of the Len function to display my string the correct way. Before my message box was returning: Or Apples Or Oranges Or Grapes where the "Or" was preceding the string. When you gave me the suggestion to use the Len function, I quickly thought of the Mid function so my code went like this:

Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim x As Variant

Set frm = Form_Form1

Set ctl = frm!lstValue

For Each varItm In ctl.ItemsSelected
    
   x = x & " or " & ctl.ItemData(varItm)
   
    
Next varItm
    

MsgBox Mid(x, 5)

so msgbox now returns Apples or Oranges or Grapes which is what I wanted.

I guess I should have started a new thread because my next question was referring to filtering a form. The two issues have nothing to do with each other they just use the same values. I'm sorry for the confusion.