CODEWITHSUNDEEP
sqlms-accesssyntaxsyntax-errorsql-view
whatwhatwhat
whatwhatwhat

Reputation: 2276

MS Access: SQL View error

I'm getting an error when pasting in a raw SQL query into Access's SQL View. I know Access syntax is a bit special but I can't figure out what it's asking for. The error says: Syntax error (missing operator) in query expression '(jobmatl.suffix = job.suffix) AND (job.job = jobmatl.job) INNER ...................... AS ibl ON jobmatl.item = ibl.item AND job.whse = ibl.whse. The error mentions everything in between what I've written.

SELECT 

    job.job, 
    job.suffix, 
    job.job_date, 
    job.item AS FG, 
    jobmatl.item,
    job.whse,
    ibl.sumofqtyonhand,
    ibl.whse

FROM 

    job 
    INNER JOIN jobmatl ON (jobmatl.suffix = job.suffix) AND (job.job = jobmatl.job)
    INNER JOIN (

        (SELECT

            i.item,
            SUM(i.qty_on_hand) AS sumofqtyonhand,
            i.whse

        FROM

            Item_by_Location_LP_ALL AS i

        WHERE

            i.hold_flag != 1

        GROUP BY

            i.item,
            i.whse

        )) AS ibl ON jobmatl.item = ibl.item AND job.whse = ibl.whse

WHERE 

    (((job.job_date)=Date()-(DatePart("w",Date(),2,1)-1)));

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

The FROM should look like this for MS Access:

FROM (job INNER JOIN
      jobmatl
      ON jobmatl.suffix = job.suffix AND job.job = jobmatl.job
     ) INNER JOIN 
     (SELECT i.item, SUM(i.qty_on_hand) AS sumofqtyonhand, i.whse
      FROM Item_by_Location_LP_ALL AS i
      WHERE i.hold_flag <> 1
      GROUP BY i.item, i.whse
     ) AS ibl
     ON jobmatl.item = ibl.item AND job.whse = ibl.whse;

MS Access requires extra parentheses for each JOIN. In addition, you have to levels of parentheses -- and I don't know if that is allowed.

Upvotes: 1

Related Questions