Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Unanswered: Syntax error (missing operator) in query expression "new response','old response"

    Hi,

    I keep getting this error while trying to validate a database with double entry. I know NOTHING about Visual Basic! However, I do know that the code works for all the other fields in this particular form, some of which are text and some of which are nummeric. The problem that I am having is only with the one that is a long text field. It will identify correctly the problem field, correct it and then will give me the above error prior to putting the information into a validation database. (I've already tried to make sure that the length of all of the possible fields is set to 255.)

    Here's the code at the point (approximately) where I have the problem:

    'If the user selected to keep the original value, set the current control equal to the original value
    If intMsgRtnVal = vbNo Then


    If OldValue = "" Then
    ctl.Value = Null
    Else
    ctl.Value = OldValue
    End If

    'Define the SQL string to add the audit record'
    strAuditSQL = "INSERT INTO tblValidationAudit ( FormName, FieldName, RecordKey, OriginalValue, ValidateValue, RetainValue, ChangeDate )" & _
    " Values('" & strFormName & "', '" & strControlName & "', '" & strKeyValue & "', '" & OldValue & "', '" & strValue & "', '" & OldValue & "', #" & Now() & "#)"

    Debug.Print strAuditSQL

    'Add the audit record
    DoCmd.RunSQL strAuditSQL

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by cihikf
    Hi,

    I keep getting this error while trying to validate a database with double entry. I know NOTHING about Visual Basic! However, I do know that the code works for all the other fields in this particular form, some of which are text and some of which are nummeric. The problem that I am having is only with the one that is a long text field. It will identify correctly the problem field, correct it and then will give me the above error prior to putting the information into a validation database. (I've already tried to make sure that the length of all of the possible fields is set to 255.)

    Here's the code at the point (approximately) where I have the problem:

    'If the user selected to keep the original value, set the current control equal to the original value
    If intMsgRtnVal = vbNo Then


    If OldValue = "" Then
    ctl.Value = Null
    Else
    ctl.Value = OldValue
    End If

    'Define the SQL string to add the audit record'
    strAuditSQL = "INSERT INTO tblValidationAudit ( FormName, FieldName, RecordKey, OriginalValue, ValidateValue, RetainValue, ChangeDate )" & _
    " Values('" & strFormName & "', '" & strControlName & "', '" & strKeyValue & "', '" & OldValue & "', '" & strValue & "', '" & OldValue & "', #" & Now() & "#)"

    Debug.Print strAuditSQL

    'Add the audit record
    DoCmd.RunSQL strAuditSQL
    It seems to me you're not telling us the whole story. For one thing, your first If statement has no End If termination. Besides, the code snippet doesn't have the words 'old response' or 'new response' in it. How about copying and pasting the whole subroutine? Better yet, why don't you run your program in interactive mode, by setting a Breakpoint in VBA and using F8 to run the code line-by-line, so you can tell exactly which line is producing the offending message?

    Sam

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    silly question:
    do you have any ' and/or " (single or double quotes) in your long text ??

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Jan 2005
    Posts
    5

    not so silly question

    That was exactly the problem!!! I hadn't even thought about it!!!

    The quotes in the text - good to explain WHY some worked and others didn't. THanks a lot!! Now I just get to make sure that no-one uses them even if they are in the raw text. Amuch more fun job


    cihikf

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a fix you might want to consider - make a function that replaces
    ' with `
    and
    " with ´
    for the save.
    you preserve a reasonable legibility, but the `´ have no special meaning for A

    (if you are fussy, you can also make a reverse function for the read so the user actually sees what he typed (unless he actually typed `or ´ of course))

    izy
    currently using SS 2008R2

Posting Permissions

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