Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Unanswered: how to consider the single-quote??

    i'm facing the poblem that when i want to enter some data into a table in a database and the data consists of one or more than one single-quotes('), a SQLException is thrown with a message. if i want to enter the text "steve's birthday", i get the message:
    "java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''steve's birthday'' ".

    i'm in a fix and am unable to think of a solution.
    please help me out.

    thanks in advance...

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: how to consider the single-quote??

    Originally posted by smartalec
    i'm facing the poblem that when i want to enter some data into a table in a database and the data consists of one or more than one single-quotes('), a SQLException is thrown with a message. if i want to enter the text "steve's birthday", i get the message:
    "java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''steve's birthday'' ".

    i'm in a fix and am unable to think of a solution.
    please help me out.

    thanks in advance...
    This is an exerpt from the Access 97 Developers Handbook from Sybex.

    I've used versions of this in my own work. It is contained in a module but my versions I've implemented in code behind forms.

    Function adhHandleQuotes(ByVal varValue As Variant, ByVal strDelimiter As String) As Variant

    ' From Access 97 Developer's Handbook
    ' by Litwin, Getz, and Gilbert (Sybex)
    ' Copyright 1997. All rights reserved.

    ' Replace all instances of strdelimiter with varValue with TWO instances,
    ' thereby handling the darned quote issue once and for all.

    ' Returns Null if varValue was Null, otherwise
    ' returns varValue with all instances of strDelimiter
    ' replaced with two of each.
    ' adhHandleQuotes("This 'is' a test", "'") returns
    ' "This ''is'' a test"

    adhHandleQuotes = adhReplace(varValue, strDelimiter, strDelimiter & strDelimiter)
    End Function

    Function adhReplace(ByVal varValue As Variant, ByVal strFind As String, ByVal strReplace As String) As Variant

    ' From Access 97 Developer's Handbook
    ' by Litwin, Getz, Gilbert (Sybex)
    ' Copyright 1997. All rights reserved.

    ' Replace all instances of strFind with strReplace in varValue.

    Dim intLenFind As Integer
    Dim intLenReplace As Integer
    Dim intPos As Integer

    If IsNull(varValue) Then
    adhReplace = Null
    Else
    intLenFind = Len(strFind)
    intLenReplace = Len(strReplace)

    intPos = 1
    Do
    intPos = InStr(intPos, varValue, strFind)
    If intPos > 0 Then
    varValue = Left(varValue, intPos - 1) & strReplace & Mid(varValue, intPos + intLenFind)
    intPos = intPos + intLenReplace
    End If
    Loop Until intPos = 0
    End If
    adhReplace = varValue
    End Function

    Good luck.

    Gregg

  3. #3
    Join Date
    Oct 2003
    Posts
    19

    Re: how to consider the single-quote??

    You can simply define a public string variable as quote:

    Public Const strquote = """"

    in a Module

    Then you'll use it like this:

    var_name = DLookup("Field", "TableName", "ID=" & var_cod & " AND (ClientType=" & strquote & strClientType & strquote & " OR ClientType='All')")

    where strClientType is a String that may include the ' char.

    Good luck

    DDD

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    What about using Chr(39) ' for the single quote

    and Chr(34)' for the double quote

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •