Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Unanswered: Apostrophe Character in String

    Hi,
    I have a dropdown box that has a text field as the control source.
    LimitToList set to No.
    When I try to enter any string containing an apostrophe (like Hopper's)
    I get the following error message:
    "Syntax error (missing operator) in query expression: '[to] = 'Hopper's".
    Is there a way to allow the apostrophe (') character in this string?

    The problem is in the toCombo_AfterUpdate procedure.
    Here is the code:
    +++++++++++++

    Dim Result
    Result = DLookup("[to]", "toTbl", "[to]='" & Me![toCombo] & "'")
    If IsNull(Result) Then
    ' Ask the user if he or she wishes to add the new value.
    Msg = Me![toCombo] & " is not in the list." & CR & CR
    Msg = Msg & "Do you want to add the new value?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
    ' If the user chose Yes, add the new value to the table.
    DoCmd.OpenForm "setupToFrm", acNormal, , , , acWindowNormal
    DoCmd.GoToRecord acDataForm, "setupToFrm", acNewRec
    Forms![setupToFrm]![to] = Me![toCombo]
    Me.Refresh
    End If
    End If
    ++++++++++++++++
    The problem is with the following line:

    Forms![setupToFrm]![to] = Me![toCombo]

    Thanks,
    John

  2. #2
    Join Date
    Jul 2004
    Posts
    125
    The error is in your result line. Single quotes are delimiters and therefore a reserved character. To avoid this issue, use the Chr(34) double quote to enclose your criteria.

    Result = DLookup("[to]", "toTbl", "[to]=" & Chr(34) & Me![toCombo] & Chr(34) & ")"

  3. #3
    Join Date
    Jul 2004
    Posts
    67

    R U Sure?

    Are you sure your error is in that line and not in the following line?...
    Result = DLookup("[to]", "toTbl", "[to]='" & Me![toCombo] & "'")
    The line you sited as the problem shouldn't be one (it should work). However, the line above needs to have two double quotation marks to allow for the possibility of an apostrophe in the string, as:
    Code:
    Result = DLookup("[to]", "toTbl", "[to]='"" & Me![toCombo] & ""'")
    That last argument is " [ t o ] = ' " " & M e ! [ t o C o m b o ] & " " ' "

    Give that a try, or tell me I'm wrong about which line is really the problem.

  4. #4
    Join Date
    Aug 2004
    Posts
    5
    Enclosing the expression in double quotes did the trick.
    Thanks.
    Cheers,
    John

  5. #5
    Join Date
    Aug 2004
    Posts
    5
    After testing, I found out that enclosing the expression in double quote didn't work. DLookup did not find existing strings.
    ineuw's solution (using Chr(34)) worked better.
    Thanks again.
    Cheers,
    John

Posting Permissions

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