Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax error (Access 2000)

    Where have I gone wrong here?, I'm getting a Syntax error (Missing Operator) in query expression "3'-BL-T76206', [REV]='1"
    Below is the code it points to (second line),,,thanks in advance..

    sSql = "UPDATE [tblSheets] SET RefDWG='" & strRefDWG & "', [DocuNo]='" & strDocuNo & "'"
    sSql = sSql & ", ConstPart='" & strConstPart & "', [REV]='" & strRev & "'"
    'sSql = sSql & " WHERE RefDWG is Null;"
    DoCmd.RunSQL sSql
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax error (Access 2000)

    It looks like strConstPart contains a single quote (after the 3); since you use single quotes around string values, this confuses SQL.

    There are several ways around this, but they will involve changing some of your code. Is there any chance that you can avoid having single quotes in your data? And in case you can't, is it possible that any of your data will contain a double quote?

  3. #3
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error (Access 2000)

    Thanks again Hans, removing the double quotes from around const part actually did the job. That simple modification now allows the code
    to execute smoothly [img]/forums/images/smilies/smile.gif[/img]

  4. #4
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error (Access 2000)

    ok, maybe I spoke too soon. It actually worked when I removed the double quotes, but not the second time around. Whats even more strange, when I restored it back to the original code
    sSql = "UPDATE [tblSheets] SET RefDWG='" & strRefDWG & "', [DocuNo]='" & strDocuNo & "'"
    sSql = sSql & ", ConstPart='" & strConstPart & "',ConstUnit='" & strConstUnit & "', REV='" & strRev & "'"
    'sSql = sSql & ", GetMod='" & Left(strconstunit, 7) & "'"
    sSql = sSql & " WHERE RefDWG is Null;"
    DoCmd.RunSQL sSql

    I didn't get the same syntax error message as before but this time I get an Invalid Sql Statement; Expected 'Delete','Insert',','Procedure','Select', or 'Update'
    Also, the data is most likely to contain single quotes and doubles quotes. [img]/forums/images/smilies/sad.gif[/img]

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Syntax error (Access 2000)

    Post the zipped excel sheet you are trying to import.

  6. #6
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error (Access 2000)

    Hi Pat,
    This excel sheet is one of many. I also managed to modify the error trapping code for the last one and apply it to the first portion that we did [img]/forums/images/smilies/smile.gif[/img].
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error (Access 2000)

    Sorry Hans, apprarently, the data that these fields refer to does not have any any quotes or double quotes.

  8. #8
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error (Access 2000)

    As I mentioned to Hans in my correction, the data that these fields refer to does not have any any quotes or double quotes.
    Incidently, these are the very first excel sheets that I ever dealt with when we began this importation saga.

  9. #9
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error (Access 2000)

    I've noticed when clicking Import, and when the error message comes up, the qryUpdatetblSheets is deleted.
    This is all strange, as it doesn't explain why it worked fine one minute but not the next.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Syntax error (Access 2000)

    I imported this sheet and I got a tblError entry that said:

    Couldn't find SHT No. or CUT SHT No. in heading

    There is something that is different between my database and yours. Would you like to zip and post it.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Syntax error (Access 2000)

    One of the first things that the import does is to delete that query. It is built "on the fly" because of the variances between sheets (eg, some sheets start in F1 or F2 or F3).

    >>This is all strange, as it doesn't explain why it worked fine one minute but not the next. <<
    What works one minute but not the next?

  12. #12
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error (Access 2000)

    Pat,
    You know the very first database that you helped me with 3 weeks ago. Thats the one, I've utiilised last weeks code for that nightmare imporation scenerio, and applied it to the first one we've made. I stayed up all night studying the code.
    The db is quite big, i can either email it to u, or paste the code here.
    I say its quite strange, because it was working perfectly today morning.

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Syntax error (Access 2000)

    All you ned to do is to delete all entries from tblSheets and compact the database.
    This compresses it quite significantly then just zip it up. It should get to below 100k and to send in this forum.

  14. #14
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error (Access 2000)

    Ok, i stripped it off all the graphics etc [img]/forums/images/smilies/smile.gif[/img]
    Attached Files Attached Files

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Syntax error (Access 2000)

    I used the email version and I went into frmImport in design mode. I went into properties and chose the Import button code.
    When you put code into forms or modules be sure that the 1st line after the Option Compare Database is a Option Explicit statement. This ensures that you define all variables. As soon as I did this and compiled the code it found a statement:
    sSql = SQL & .....
    The word SQL is undefined and should be renamed to sSql, then compile the code again. At this point you should get no errors.
    Try testing the Import again after doing all this and it should work. I have tried it on mine and it works just great.

Page 1 of 3 123 LastLast

Posting Permissions

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