UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Syntax Error In SQL Query, Access 2016    
 
   
RFree190
post Aug 9 2018, 02:42 PM
Post#1



Posts: 267
Joined: 18-December 06
From: Southern California


I'm trying to run a query, but keep getting an error;
Syntax Error (missing operator in query expression 'pies.[Part Number (B15)] = qryFitment.PART_NO INNER JOIN PriceFile_July2018 AS PriceFile ON pies.[Part Number (B15)] = PriceFile.[CatalogNo'.

This is the FROM section of my SQL. Anyone see any errors in here that I'm not spotting?

CODE
FROM ((
    EXP_APP_INFO_June_2018 AS app_info
    RIGHT JOIN MagnaFlow_PIES_June_2018 AS pies ON app_info.PART_NO = pies.[Part Number (B15)])
    LEFT JOIN Conversion ON (pies.[Part Description - Short (C10)] = Conversion.desc) AND (pies.[Emission Code (E10)] = Conversion.ems))
    LEFT JOIN qryFitment ON pies.[Part Number (B15)] = qryFitment.PART_NO
    INNER JOIN PriceFile_July2018 AS PriceFile ON pies.[Part Number (B15)] = PriceFile_July2018.[Catalog No]
    
GROUP BY pies.[Part Number (B15)]

HAVING (((First(pies.[MAP Price (D40)]))>0));
Go to the top of the page
 
Daniel_Stokley
post Aug 9 2018, 03:09 PM
Post#2



Posts: 270
Joined: 22-December 14
From: Grand Junction, CO, USA


The two opening parenthesis after FROM and the two closing parenthesis in the middle of the FROM clause seem suspicious frown.gif
Go to the top of the page
 
RJD
post Aug 9 2018, 03:21 PM
Post#3


UtterAccess VIP
Posts: 8,652
Joined: 25-October 10
From: Gulf South USA


INNER JOIN PriceFile_July2018 AS PriceFile ON pies.[Part Number (B15)] = PriceFile_July2018.[Catalog No]

The first thing I saw was that you aliased PriceFile_July2018 As PriceFile, then did not use the alias later with the field [Catalog No].

However, I am going a bit cross-eyed with the way you have named your fields, with spaces and special characters. Plus the parens.

See if changing the table name to the alias when stating the ON field will make a difference. Failing that, perhaps you could post a db with the relevant objects so we could test and look deeper into this.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RFree190
post Aug 9 2018, 03:23 PM
Post#4



Posts: 267
Joined: 18-December 06
From: Southern California


Yeah, it [censored]. That's the way a manufacturer sends us the data.
Ha! Apparently, the word Suc#s gets censored... :-)

This post has been edited by RFree190: Aug 9 2018, 03:24 PM
Go to the top of the page
 
RJD
post Aug 9 2018, 03:25 PM
Post#5


UtterAccess VIP
Posts: 8,652
Joined: 25-October 10
From: Gulf South USA


...and, like Daniel, I was also going cross-eyed with the parens as well - and got thrown off by you using parens inside the field names. I kept counting the parens and getting a different number every time! Check those again, and do get rid of the parens inside the field names - bad practice anyway. Looks like the parens may be balanced - but best to check those again.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
BruceM
post Aug 10 2018, 06:44 AM
Post#6


UtterAccess VIP
Posts: 7,574
Joined: 24-May 10
From: Downeast Maine


I processed the SQL in Notepad++. The count of opening and closing parentheses matches, and they seem to line up. Here is the FROM section with the parentheses paired:
CODE
FROM
  (
    (
      EXP_APP_INFO_June_2018 AS app_info
        RIGHT JOIN MagnaFlow_PIES_June_2018 AS pies
        ON app_info.PART_NO = pies.[Part Number (B15)]
    )
    LEFT JOIN Conversion
    ON
      (
        pies.[Part Description - Short (C10)] = Conversion.desc
      )
        AND
      (
        pies.[Emission Code (E10)] = Conversion.ems
      )
  )
  LEFT JOIN qryFitment
  ON pies.[Part Number (B15)] = qryFitment.PART_NO
    INNER JOIN PriceFile_July2018 AS PriceFile
    ON pies.[Part Number (B15)] = PriceFile_July2018.[Catalog No]

It looks to me as if the first section, before the last parentheses (not counting the ones in field names) is, in effect, a query. As such, either it needs to be saved as a query (qryTest for now), and it becomes:

FROM qryTest
LEFT JOIN qryFitment

Or else alias the SQL:

) AS MyAlias
LEFT JOIN qryFitment

In other words, you have SELECT {Fields} FROM ... what? Before you can left join to qryFitment you need to establish what is being joined.

In either case, you then select from the query name or alias, rather than on the table and field names within that SQL.

That's how it looks from here, but I may be missing or misinterpreting something.

As for the HAVING, you can get rid of the parentheses except for the ones used by First. Regarding that, first what? First, in query grouping, is Access-specific, I believe. It depends on the sort order, as I recall. It's hard to know exactly what you are trying to do, but perhaps Min would be better.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th August 2018 - 02:42 PM