Access SQL Syntax error: missing operator

Question

I am trying to convert a T-SQL query to MS Access SQL and getting a syntax error that I am struggling to find. My MS Access SQL query looks like this:

INSERT INTO IndvRFM_PreSort (CustNum, IndvID, IndvRScore, IndRecency, IndvFreq, IndvMonVal )
    SELECT 
        IndvMast.CustNum, IndvMast.IndvID, IndvMast.IndvRScore, 
        IndvMast.IndRecency, IndvMast.IndvFreq, IndvMast.IndvMonVal
    FROM 
        IndvMast 
    INNER JOIN 
        OHdrMast ON IndvMast.IndvID = OHdrMast.IndvID 
    INNER JOIN 
        MyParameterSettings on 1=1].ProdClass
    INNER JOIN 
        [SalesTerritoryFilter_Check all that apply] ON IndvMast.SalesTerr = [SalesTerritoryFilter_Check all that apply].SalesTerr
    WHERE 
        (((OHdrMast.OrdDate) >= [MyParameterSettings].[RFM_StartDate]))
    GROUP BY 
        IndvMast.CustNum, IndvMast.IndvID, IndvMast.IndvRScore, 
        IndvMast.IndRecency, IndvMast.IndvFreq, IndvMast.IndvMonVal, 
        [CustTypeFilter_Check all that apply].IncludeInRFM, 
        [ProductClassFilter_Check all that apply].IncludeInRFM, 
        [SourceCodeFilter_Check all that apply].IncludeInRFM, 
        IndvMast.FlgDontUse

I have reviewed differences between MS Access SQL and T-SQL at http://rogersaccessblog.blogspot.com/2013/05/what-are-differences-between-access-sql.html and a few other locations but with no luck.

All help is appreciated.

update: I have removed many lines trying to find the syntax error and I am still getting the same error when running just (which runs fine using T-SQL):

SELECT 
    IndvMast.CustNum, IndvMast.IndvID, IndvMast.IndvRScore, 
    IndvMast.IndRecency, IndvMast.IndvFreq, IndvMast.IndvMonVal
FROM 
    IndvMast 
INNER JOIN 
     OHdrMast  ON IndvMast.IndvID = OHdrMast.IndvID    
INNER JOIN 
     [My Parameter Settings] ON 1 = 1

Are you missing a final parenthesis in the HAVING statement? — Mark C.
– Mark C., Commented Jan 11, 2017 at 15:56
@peggy please re-post your query. what's up with 1=1apply]? — Stephen
– Stephen, Commented Jan 11, 2017 at 16:14
@peggy you still have MyParameterSettings on 1=1].ProdClass? — Stephen
– Stephen, Commented Jan 11, 2017 at 16:22

Parfait · Accepted Answer · 2017-01-11 23:00:24Z

There are a number of items in your query that should also have failed in any SQL-compliant database:

You have fields from tables in GROUP BY not referenced in FROM or JOIN clauses.
Number of fields in SELECT query do not match number of fields in INSERT INTO clause.
The MyParameterSettings table is not properly joined with valid ON expression.

Strictly MS Access SQL items:

For more than one join, MS Access SQL requires paired parentheses but even this can get tricky if some tables are joined together and their paired result joins to outer where you get nested joins.
Expressions like ON 1=1 must be used in WHERE clause and for cross join tables as MyParameterSettings appears to be, use comma-separated tables.
For above reasons and more, it is advised for beginners to this SQL dialect to use the Query Design builder providing table diagrams and links (if you have the MS Access GUI .exe of course). Then, once all tables connect graphically with at least one field selected, jump into SQL view for any nuanced scripting logic.

Below is an adjustment to SQL statement to demonstrate the parentheses pairings and for best practices, uses table aliases especially with long table names.

INSERT INTO IndvRFM_PreSort (CustNum, IndvID, IndvRScore, IndRecency, IndvFreq, IndvMonVal)

SELECT 
    i.CustNum, i.IndvID, i.IndvRScore, i.IndRecency, i.IndvFreq, i.IndvMonVal
FROM 
    [MyParameterSettings] p, (IndvMast i
INNER JOIN 
    OHdrMast o ON i.IndvID = o.IndvID) 
INNER JOIN 
    [SalesTerritoryFilter_Check all that apply] s ON i.SalesTerr = s.SalesTerr
WHERE 
    (o.OrdDate >= p.[RFM_StartDate])
GROUP BY 
    i.CustNum, i.IndvID, i.IndvRScore, i.IndRecency, i.IndvFreq, i.IndvMonVal

And in your smaller SQL subset, the last table does not need an ON 1=1 condition and may be redundant as well in SQL Server. Simply a comma separate table will suffice if you intend for cross join. The same is done in above example:

SELECT  
    IndvMast.CustNum, IndvMast.IndvID, IndvMast.IndvRScore, 
    IndvMast.IndRecency, IndvMast.IndvFreq, IndvMast.IndvMonVal
FROM 
    [My Parameter Settings], IndvMast
INNER JOIN 
     OHdrMast ON IndvMast.IndvID = OHdrMast.IndvID

Joe Taras · Accepted Answer · 2017-01-11 15:58:12Z

1

I suppose there are some errors in your query, the first (more important).

Why do you use HAVING clause to add these conditions?

HAVING (((IndvMast.IndRecency)>(date()-7200))
AND (([CustTypeFilter_Check all that apply].IncludeInRFM)=1)
AND (([ProductClassFilter_Check all that apply].IncludeInRFM)=1)
AND (([SourceCodeFilter_Check all that apply].IncludeInRFM)=1)
AND ((IndvMast.FlgDontUse) Is Null))

HAVING usually used about conditions on aggregate functions (COUNT, SUM, MAX, MIN, AVG), for scalar value you must put in WHERE clause.

The second: You have 12 parenthesis opened and 11 closed in HAVING clause

answered Jan 11, 2017 at 15:58

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

1 Comment

GregH Over a year ago

The query works fine in sql with the having clause. Added a closing parenthesis and still getting

Syntax Error(missing operator) in query espression 'IndvMast.IndvID = OHdrMast.IndvID ........ = [SalesTerritoryFilter_Check all that apply].SalesTer'

Collectives™ on Stack Overflow

Access SQL Syntax error: missing operator

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related