pavanbh
asked on
Pulling up a text field containing single quote.
I have to store a single quote(') as a part of one of the text fields in the Access database, which I want to retrieve from the visual basic using DAO connectivity. But visual basic would not pull up the records containing the single quote. It gives an error saying there is a syntax problem.
Example:
With rs
.FindFirst "Insurer = '" & cmbEdConIns.Text & "'"
How do I get around this?
Thanks.
Example:
With rs
.FindFirst "Insurer = '" & cmbEdConIns.Text & "'"
How do I get around this?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
All the records in the DB that have single quotes are bad and must be replaced. I always use the following code block to handle single quotes and to surrond the string in single quotes. IE inQuotes("Mc'mann") becomes "'Mc''mann"
Public Function InQuotes(StringToUse As String) As String
' Puts apostrophes (') around a string
' Also checks for any apostrophes (') in a string (e.g. the name O'Reilly)
' and replaces these with 2 apostrophes (e.g. O''Reilly) so that
' they will be accepted by SYBASE and ACCESS.
' If the string passed in is empty (""), then we return the word 'null'.
Dim iPointInStr As Integer
If Len(Trim(StringToUse)) = 0 Then
' Empty string, return null
InQuotes = "null"
Else
' String is not empty, put apostrophes around it.
' Search for the first instance of a ' in a string
iPointInStr = InStr(StringToUse, "'")
' Loop thru the string until we don't find any more ' characters
Do While iPointInStr > 0
' Get the first section of the string (before ')
StringToUse = Left(StringToUse, iPointInStr - 1) & _
"''" & Mid(StringToUse, iPointInStr + 1)
' Get the next point in the string
iPointInStr = InStr(iPointInStr + 2, StringToUse, "'")
Loop
InQuotes = "'" & StringToUse & "'"
End If
End Function
Public Function InQuotes(StringToUse As String) As String
' Puts apostrophes (') around a string
' Also checks for any apostrophes (') in a string (e.g. the name O'Reilly)
' and replaces these with 2 apostrophes (e.g. O''Reilly) so that
' they will be accepted by SYBASE and ACCESS.
' If the string passed in is empty (""), then we return the word 'null'.
Dim iPointInStr As Integer
If Len(Trim(StringToUse)) = 0 Then
' Empty string, return null
InQuotes = "null"
Else
' String is not empty, put apostrophes around it.
' Search for the first instance of a ' in a string
iPointInStr = InStr(StringToUse, "'")
' Loop thru the string until we don't find any more ' characters
Do While iPointInStr > 0
' Get the first section of the string (before ')
StringToUse = Left(StringToUse, iPointInStr - 1) & _
"''" & Mid(StringToUse, iPointInStr + 1)
' Get the next point in the string
iPointInStr = InStr(iPointInStr + 2, StringToUse, "'")
Loop
InQuotes = "'" & StringToUse & "'"
End If
End Function
DB cleanup, due november 5th, 2003.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
-Accept comment TimCottee
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
MYLim
EE Cleanup Volunteer
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
-Accept comment TimCottee
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
MYLim
EE Cleanup Volunteer
ASKER
That was nice. I could get rid of the error but it didn't pull up the record. It could not find a match for that expression. May be I'm missing something more.
Thanks.