+ Reply to Thread
Results 1 to 16 of 16

SQL Syntax Error

  1. #1
    Registered User
    Join Date
    01-27-2011
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    16

    SQL Syntax Error

    Hi Everyone,

    I'm creating a macro which uses a query to search a database using two variables to define the search. When I get to the line .Refresh BackgroundQuery:=False (Highlighted in code), I get a SQL Syntax error message. Here's my code, any ideas why this is happening?
    Please Login or Register  to view this content.
    Thanks for your help!
    Last edited by JoWales; 02-25-2011 at 12:35 PM. Reason: To provide clarity of the issue

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,980

    Re: SQL Syntax Error

    You are passing the literal string 'untill' in your SQL, not concatenating the variable into it.

    Note: please use code tags whenever you post code as required by the forum rules. I have added them for you on this occasion but you must do it yourself in future, please.
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    01-27-2011
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: SQL Syntax Error

    Quote Originally Posted by romperstomper View Post
    You are passing the literal string 'untill' in your SQL, not concatenating the variable into it.
    So you're saying that currently I'm literally inserting the word "untill" into my query rather than the variable contained within "untill"? How do I refer to the variable then?


    Thanks for the heads up on the code tags, I'll do that in the future
    Last edited by JoWales; 02-25-2011 at 11:01 AM. Reason: Corrected a spelling mistake

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,980

    Re: SQL Syntax Error

    Correct. Instead of:
    Please Login or Register  to view this content.
    you need:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-27-2011
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: SQL Syntax Error

    Ok thanks. I've edited the code as you suggested but I am still receiving the same error. I also then changed the code where the other variable (from) is used , as I suspected it had the same error, from:

    Please Login or Register  to view this content.

    To:

    Please Login or Register  to view this content.

    But still no luck. Any other advice on what the problem may be? Thanks again.

  6. #6
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,980

    Re: SQL Syntax Error

    Your From date check appears to have a random '20' on the front of it, which won't be helping. I also can't see the point of aliasing a table with its actual name. Try using:
    Please Login or Register  to view this content.
    and see if that helps.

  7. #7
    Registered User
    Join Date
    01-27-2011
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: SQL Syntax Error

    Thanks again, but it's still showing the same error

  8. #8
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,980

    Re: SQL Syntax Error

    Maybe date format - try:
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-27-2011
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: SQL Syntax Error

    Unfortunately not. I tried other date formats too to no avail.

  10. #10
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,980

    Re: SQL Syntax Error

    Have you checked the values of From and Untill? Also, have you tried recording a macro of setting up this connection? (or is that what this is - if so, what was the original code?)

  11. #11
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    365
    Posts
    1,256

    Re: SQL Syntax Error

    Should it be "d" instead of "ts" since there's no time?
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Hope that helps,

    Colin

    RAD Excel Blog

  12. #12
    Registered User
    Join Date
    01-27-2011
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: SQL Syntax Error

    Quote Originally Posted by romperstomper View Post
    Have you checked the values of From and Untill? Also, have you tried recording a macro of setting up this connection? (or is that what this is - if so, what was the original code?)
    From and untill are in the format dd/mm/yyy and seem ok to me. Here is the original code of the recording:


    Please Login or Register  to view this content.

    I just re-made this because I didn't have a copy of the original code dorry, but I followed the same steps in creating this so it should be the same.

    I replaced the dates with the variables from and untill again but no luck.

  13. #13
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,980

    Re: SQL Syntax Error

    Based on that and Colin's post, you probably need:
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    01-27-2011
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: SQL Syntax Error

    Awesome! That did it! Thanks so much guys, I really appreciate it. I'll post the finished code so that anyone who reads this in the future can see the final version:


    Please Login or Register  to view this content.

    Thanks again guys, you were a massive help.

  15. #15
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    365
    Posts
    1,256

    Re: SQL Syntax Error

    Hi Jo,

    One last, small detail. It doesn't matter in this case because the mm follows on after hh (so the Format function knows to use minutes instead of months), but I recommend that you get in the habit of using "n" for minutes.
    eg.
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    01-27-2011
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: SQL Syntax Error

    Ok Colin, thanks for the tip!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1