+ Reply to Thread
Results 1 to 10 of 10

MS Query - Syntax Error

  1. #1
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    MS Query - Syntax Error

    I didn't know where to ask this so thought I would try here.

    I am running MS Query out of our financial database. When I do I get the error 31480 syntax error.....can someone tell me what this is or how it can be by-passed. I am running a query from a table that has our AP checks and for whatever reason it doesn't like the column that says check. When I remove that column the query will run, but what's the point if I can't have the check number in the query.

    Thanks,

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: MS Query - Syntax Error

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Re: MS Query - Syntax Error

    There isn't anything that I can show, but a screen shot.....the query won't run and the limit on attachments prevents showing an actual query and even if I could the query requires an ODBC connection......so not sure exactly what I could show here.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: MS Query - Syntax Error

    I work a lot with MS-Query; it has its peculiarities. For one, it does not like UDFs or proprietary code (like Access' NZ() function). So if the check depends on one of these, you may have to SQL-Code around it.

    I'm fortunate enough to have a TOAD license, but I've also used WinSQL to test my code before using MS-Query. If it runs there, it most likely will run in MS-Query. TOAD and WinSQL have better debugging tools than MS-Query (which has NONE).

    Also, I've all but given up on the wizard for MS-Query. It is useless for all but the most basic queries. I have the option to use the wizard clicked off. I develop my queries in TOAD and then bypass the table offerings when I open MS-Query and go straight to the SQL icon and copy and paste the code in there.

    Also consider this. Import the data without the checks and do the checks in the Excel side. Since the data comes in as an Excel table, you can set these formulas up and the table will "remember" them and copy them down automatically.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  5. #5
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Re: MS Query - Syntax Error

    dflak,
    I don't use the wizard either. I can pull the tables in and link them as needed and can set the criteria just fine. I just haven't ever had an issue where one of the tables gives me an error like this and it's a key field. For instance I can't just put this in excel as I need the check number that the table has so that I can give this to audit. So far I have found 3 varying columns that this ODBC link is having issues with, Limit, Check and can't remember the 3rd. In this case I am just pulling in the table and trying to run it with every column in the table then it throws up this error. As soon as I delete the check column the query runs. I wasn't sure if there was a work around for this or not?

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: MS Query - Syntax Error

    I do little work in MS_Query. It's all handled on the Oracle side. I don't select tables and join them in MS-Query. I write the SQL, run it in TOAD and then copy and past the "good" SQL script into MS-Query or even the command text for the connection.

  7. #7
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Re: MS Query - Syntax Error

    ok, that's probably above my skill set....I was hoping for a simple work around as the check column is in the table and populates when you post the check so it's a number that's it. Just can't seem to figure out how to by-pass it.

    Thanks,

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: MS Query - Syntax Error

    It seems simple enough. Download the data without the check and on the excel side add a helper column =ISNUMBER([@[Check Number]]. This will evaluate to True or False and you can even put a conditional format or filter on it.

  9. #9
    Registered User
    Join Date
    05-15-2019
    Location
    Des Moines, Iowa
    MS-Off Ver
    Office 365
    Posts
    43

    Re: MS Query - Syntax Error

    dflak,
    Thanks but I was trying to figure it out all within the query. But I did get a solution. In MS Query I can hit the SQL button and simply put square brackets around the column that's giving me an error. I did that and the query did run.

    Thanks again

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: MS Query - Syntax Error

    Thanks for the update. One more thing to add to my list of quirks for MS-Query.

    MS-Query used to have / maybe still does have a quirk with aliases. It didn't like them; it ignored them. For example, if you had a CASE statement the whole freakin' thing would wind up as the column header. The work around for this is to put parentheses around the table names in the FROM statement. Very obvious.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Syntax error with simple MS Query
    By asmith5rgv in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-17-2017, 04:01 PM
  2. [SOLVED] Syntax error (Missing operator) in query expression
    By lengwer in forum Access Tables & Databases
    Replies: 2
    Last Post: 08-22-2013, 04:00 PM
  3. Syntax Error in Query Expression
    By acmagyar in forum Access Programming / VBA / Macros
    Replies: 0
    Last Post: 07-12-2013, 03:11 PM
  4. Syntax Error (missing Operator) in query expression
    By AnthonyWB in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-25-2011, 11:59 AM
  5. MS Query import from Excel - Syntax Error
    By EstherJ in forum Excel General
    Replies: 1
    Last Post: 04-24-2006, 04:44 AM
  6. Syntax Error in Excel Query for DATE field
    By James T in forum Excel General
    Replies: 3
    Last Post: 08-31-2005, 08:05 AM
  7. [SOLVED] Syntax Error - Using Microsoft Query and ODBC link to a table
    By James T in forum Excel General
    Replies: 0
    Last Post: 08-30-2005, 10:05 PM

Tags for this Thread

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