CODEWITHSUNDEEP
sqlms-access
angiee
angiee

Reputation: 21

SQL query in Ms access

Can anyone tell me what is wrong with this query, it says syntax error.But syntax looks good for me.

SELECT Errortypereport.[Job Design Control],
       Errortypereport.[Detailer Name], Errortypereport.SuppilerName,
       Errortypereport.[Part Number], Errortypereport.[Change Level],
       Errortypereport.[Event Type Selected], Errortypereport.[Tracking Number],
       Errortypereport.[Event Date], Errortypereport.[Error Type Selected],
       Errortypereport.[Sub Type Selected], Errortypereport.ErrorSubTypeComment,
       Errortypereport.[Fundamental Errors Qty],
       Errortypereport.[Information Errors Qty],
       Errortypereport.[Readability Errors Qty], Errortypereport.[Error Sub Type Ref],
       Errortypereport.Enteredby, Errortypereport.[Wrapper engineer]
FROM Errortypereport INNER JOIN
     POSBLD ON (Errortypereport.Part Number)=(POSBLD.PartNumber)
           And (Errortypereport.Change Level)=( POSBLD.ChangeLevel);

error message is: syntax error (missing operator) in query expression '(Errortypereport.Part Number)=(POSBLD.PartNumber) And (Errortypereport.Change Level)=( POSBLD.ChangeLevel)'

Upvotes: 2

Views: 125

Answers (4)

Taryn
Taryn

Reputation: 247610

You are missing some brackets on your JOIN. I would also use a table alias for readability.

SELECT 
    E.[Job Design Control], 
    E.[Detailer Name], 
    E.SuppilerName, 
    E.[Part Number], 
    E.[Change Level], 
    E.[Event Type Selected], 
    E.[Tracking Number], 
    E.[Event Date], 
    E.[Error Type Selected], 
    E.[Sub Type Selected], 
    E.ErrorSubTypeComment, 
    E.[Fundamental Errors Qty], 
    E.[Information Errors Qty], 
    E.[Readability Errors Qty], 
    E.[Error Sub Type Ref], 
    E.Enteredby, 
    E.[Wrapper engineer]
FROM Errortypereport E 
INNER JOIN POSBLD 
    ON (E.[Part Number])=(POSBLD.PartNumber) 
        AND (E.[Change Level])=(POSBLD.ChangeLevel);

Upvotes: 0

Alex K.
Alex K.

Reputation: 175748

If a column name is a reserved word or contains a space; Errortypereport.Part Number it must be escaped with [] such that you use; Errortypereport.[Part Number].

Upvotes: 3

JonH
JonH

Reputation: 33141

If you have spaces in column names you need to wrap them around [ ]'s.

There is a space between Part and Number in your On as well as change level

ON (Errortypereport.Part Number)=(POSBLD.PartNumber) And (Errortypereport.Change Level)=( POSBLD.ChangeLevel);

Change it to:

ON (Errortypereport.[Part Number])=(POSBLD.PartNumber) And (Errortypereport.[Change Level])=( POSBLD.ChangeLevel);

Upvotes: 4

Beth
Beth

Reputation: 9607

try

ON Errortypereport.[Part Number]

and

Errortypereport.[Change Level]

Upvotes: 3

Related Questions