Name function and use of an apostrophe

[David Hewitt]

I have a Visual Basic program which uses an Access database.

I use the normal Add, Change, Delete, OK, and Cancel functions for
adding to, altering or deleting and these work fine.

I then use a Find function to select one particular record.

If the Name field contains an apostrophe I get an error.

The coding that I am using is as follows:-

Private Sub cmdSelectRecipe_Click()

'Select Recipe

Data1.Recordset.FindFirst "RecipeName like" & "'" & DataList1 & "*"
& "'"
cmdSelectRecipe_OK:

End Sub

any suggestions, please, as to how I can make it accept an apostrophe
without giving rise to an error message.

David

 

[John Vinson]

Private Sub cmdSelectRecipe_Click()

'Select Recipe

Data1.Recordset.FindFirst "RecipeName like" & "'" & DataList1 & "*"
& "'"
cmdSelectRecipe_OK:

End Sub

any suggestions, please, as to how I can make it accept an apostrophe

It's seeing the apostrophe as the closing apostrophe of the criterion.
If the DataList1 will *never* contain a quote character " you can use
" - ASCII value 34 - to delimit the criterion instead of ':

Data1.Recordset.FindFirst "RecipeName like" & Chr(34) & DataList1 &
"*" & Chr(34)

If you might have both apostrophes AND doublequotes in the string,
then you can "double up" whichever character you're using as the
delimiter. That is, the query

RecipeName LIKE "Aunt Hazel''s Brownies*"

will find Aunt Hazel's Brownies. To do this, use the Replace()
function:

"RecipeName LIKE '" & Replace(DataList1, "'", "''") & "*'")

Note also that you don't need to have separate string constants for
the fieldname and the quote character or the asterisk.

John W. Vinson[MVP]

 

[David Hewitt]

John,

Thanks, that worked fine.

David

It's seeing the apostrophe as the closing apostrophe of the criterion.
If the DataList1 will *never* contain a quote character " you can use
" - ASCII value 34 - to delimit the criterion instead of ':

Data1.Recordset.FindFirst "RecipeName like" & Chr(34) & DataList1 &
"*" & Chr(34)

If you might have both apostrophes AND doublequotes in the string,
then you can "double up" whichever character you're using as the
delimiter. That is, the query

RecipeName LIKE "Aunt Hazel''s Brownies*"

will find Aunt Hazel's Brownies. To do this, use the Replace()
function:

"RecipeName LIKE '" & Replace(DataList1, "'", "''") & "*'")

Note also that you don't need to have separate string constants for
the fieldname and the quote character or the asterisk.

John W. Vinson[MVP]