peggy last month

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 last month

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 last month
Viewed 3,240 times
Voted 13
Answered 1 times

Search




Leave an answer