Quotation marks and List Boxes

[Colin597]

I have a list of key terms that I have placed in quotation marks to facilitate searches by these key terms e.g. 'Advisers'. Unfortunately, when I try to assign the key terms to a record using code to transfer selected terms from one list box to another, the quotation marks are lost in the transfer. Moreover, if the term includes an apostrophe, the text following the apostrophe is not copied into the recipient list box. Is the use of quotation marks imparting an instruction that I am not aware of. Advice welcome.

 

[pbaldy]

Yes, quotes in the data can cause problems. What is your code? See if this helps:

http://www.baldyweb.com/BuildSQL.htm

 

[Colin597]

Paul,

Your linked document is very useful. Obviously, single quotation marks are to be avoided as a means of delineating one key term from another.

You asked to see the coding of my sub-routine. Please find it below:

Private Sub ButtonAdd_Click()
Dim i As Integer, CountSelection As Integer

' Clear contents from Search List Box
If lbSearchCriteria.ListCount <> 0 Then
For i = (lbSearchCriteria.ListCount - 1) To 0 Step -1
If lbSearchCriteria.ListCount = 0 Then Exit For
lbSearchCriteria.Selected(i) = True
lbSearchCriteria.RemoveItem lbSearchCriteria.ListIndex
Next i
End If

' Count number of Key Words selected
CountSelection = 0
For i = 0 To lbKeyWords.ListCount - 1
If lbKeyWords.Selected(i) Then
CountSelection = CountSelection + 1
End If
Next i

' Exit sub-routine if the user does not select any Key Words
If CountSelection = 0 Then Exit Sub

' Add selected Key Words to Search List
For i = 0 To lbKeyWords.ListCount - 1
If lbKeyWords.Selected(i) Then
lbSearchCriteria.AddItem lbKeyWords.ItemData(i)
End If
Next i

' Leave nothing selected in Search List
For i = 0 To lbSearchCriteria.ListCount - 1
lbSearchCriteria.Selected(i) = False
Next i

' Ensure that Remove Button is enabled
ButtonRemove.Enabled = True
End Sub

The lbKeyWords.Item(i) captures the key term with the single quotation marks but the lbSearchCriteria.AddItem removes them.

Can you recommend a better way to delineate key terms so that a search using the key term - project - doesn't return records tied to the key term - project manager.

Regards

Colin