Custom Search String

[Max Smart]

My contacts database (Access 2000) has a custom search function to locate
last names. I use the following code in the Change event of my text box...

Private Sub txtLocate_Change()
If Trim((txtLocate.Text) & "") <> "" Then
strLocate = Trim((txtLocate.Text) & "")
CreateSQL
End If
End Sub

The Create SQL function builds an SQL string and populates a listbox on the
main contacts form using code like this...

strSelect = (my selection criteria goes here)
strWhere = "Contacts.[Last Name] LIKE '" & strLocate & "*'"
strOrderBy = (my order by string goes here)
strSQL = strSelect & " " & strWhere & " " & strOrderBy
lstContacts.RowSource = strSQL

The code work fine until I search for names with apostrophes (O'Connor,
etc.) - the listbox comes up blank.

What am I doing wrong???

Thanks,
Max

 

[Dirk Goldgar]

My contacts database (Access 2000) has a custom search function to
locate last names. I use the following code in the Change event of my
text box...

Private Sub txtLocate_Change()
If Trim((txtLocate.Text) & "") <> "" Then
strLocate = Trim((txtLocate.Text) & "")
CreateSQL
End If
End Sub

The Create SQL function builds an SQL string and populates a listbox
on the main contacts form using code like this...

strSelect = (my selection criteria goes here)
strWhere = "Contacts.[Last Name] LIKE '" & strLocate & "*'"
strOrderBy = (my order by string goes here)
strSQL = strSelect & " " & strWhere & " " & strOrderBy
lstContacts.RowSource = strSQL

The code work fine until I search for names with apostrophes
(O'Connor, etc.) - the listbox comes up blank.

What am I doing wrong???

Thanks,
Max

Either use double-quotes to surround the string literal in in building
the SQL string, or double-up the single-quotes inside the literal. For
greatest safety, use double-quotes *and* double up any double-quotes
that may appear inside strLocate:

strWhere = _
"Contacts.[Last Name] LIKE " & Chr(34) & _
Replace(strLocate, Chr(34), Chr(34) & Chr(34)) & _
"*" & Chr(34)

 

[Max Smart]

Thanks! I'll give it a try.

Max

My contacts database (Access 2000) has a custom search function to
locate last names. I use the following code in the Change event of my
text box...

Private Sub txtLocate_Change()
If Trim((txtLocate.Text) & "") <> "" Then
strLocate = Trim((txtLocate.Text) & "")
CreateSQL
End If
End Sub

The Create SQL function builds an SQL string and populates a listbox
on the main contacts form using code like this...

strSelect = (my selection criteria goes here)
strWhere = "Contacts.[Last Name] LIKE '" & strLocate & "*'"
strOrderBy = (my order by string goes here)
strSQL = strSelect & " " & strWhere & " " & strOrderBy
lstContacts.RowSource = strSQL

The code work fine until I search for names with apostrophes
(O'Connor, etc.) - the listbox comes up blank.

What am I doing wrong???

Thanks,
Max

Either use double-quotes to surround the string literal in in building
the SQL string, or double-up the single-quotes inside the literal. For
greatest safety, use double-quotes *and* double up any double-quotes
that may appear inside strLocate:

strWhere = _
"Contacts.[Last Name] LIKE " & Chr(34) & _
Replace(strLocate, Chr(34), Chr(34) & Chr(34)) & _
"*" & Chr(34)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)