Home Ask Login Register

Developers Planet

Your answer is one click away!

peggy January 2017

Access Sql Syntax error: Missing operator

I am trying to convert a tsql query to access sql and getting a syntax error that I am struggling to find. My access sql query looks like the following:

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
    INNER JOIN [SourceCodeFilter_Check all that apply] 
        ON OHdrMast.SourceCode = [SourceCodeFilter_Check all that apply].SourceCode
    INNER JOIN [CustTypeFilter_Check all that apply] 
        ON IndvMast.CType1 = [CustTypeFilter_Check all that apply].CType
    INNER JOIN ODetMast 
        ON OHdrMast.[Order] = ODetMast.[Order]
    INNER JOIN [ProductClassFilter_Check all that apply] 
        ON ODetMast.ProdClass = [ProductClassFilter_Check all that apply].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
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))
ORDER BY IndvMast.IndRecency, IndvMast.IndvFreq, IndvMast.IndvMonVal;

I have reviewed differences between access sql and tsql at

Answers


Joe Taras January 2017

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.

Post Status

Asked in January 2017
Viewed 3,240 times
Voted 13
Answered 1 times

Search




Leave an answer


Quote of the day: live life

Devs Planet ®

2014-2016 www.devsplanet.com

Devs Planet © all rights reserved

Search