peggy 5 days ago

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 5 days ago

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 5 days ago
Viewed 3,240 times
Voted 13
Answered 1 times

Search




Leave an answer