help with query error

pmcm

New member
Local time
Today, 17:53
Joined
Jul 20, 2012
Messages
6
Hi can anyone help me with the following query it is giving me this error:
"The LEVEL clause includes a reserved word or argument that is misspelled or missing, or the punctuation is incorrect."

here's the query
Code:
SELECT 
DateValue([Finished]) AS [Date],
Paintshop_tblProduction.PartNo, 
Paintshop_tblProduction.Description, 
Sum(Paintshop_tblProduction.[Actual Qty Produced]) AS Total, 
Paintshop_tblRoutings.OpDescription, 
Paintshop_tblRoutings.Powder_Usage, 
(([Powder_Usage]*Sum([Actual Qty Produced]))/1000) AS [Planned Powder Usage (Kg)], 
Paintshop_tblRoutings.QtyPerJig, 
(Sum([Actual Qty Produced])/[QtyPerJig]) AS [Jigs Painted], 
Paintshop_tblRoutings.[Area (m2)] AS [Area per panel (m2)], 
Paintshop_tblRoutings.[Wrap Around (%)], 
([Area (m2)]*(1+(([Wrap Around (%)]/100))/100)*(Sum([Actual Qty Produced]))) AS [Total Area (m2)], 
(Sum([Actual Qty Produced])/[QtyPerJig])*(50/1000) AS [Powder lost to Jigs (Kg)]

FROM Paintshop_tblProduction 
LEFT JOIN Paintshop_tblRoutings ON Paintshop_tblProduction.PartNo = Paintshop_tblRoutings.PartNo;

GROUP BY DateValue([Finished]), 
Paintshop_tblProduction.PartNo, 
Paintshop_tblProduction.Description, 
Paintshop_tblRoutings.OpDescription, 
Paintshop_tblRoutings.Powder_Usage, 
Paintshop_tblRoutings.QtyPerJig, 
Paintshop_tblRoutings.[Area (m2)], 
Paintshop_tblRoutings.[Wrap Around (%)]

HAVING (((DateValue([Finished])) Between '24/04/2012 00:00:00' And '24/04/2012 23:59:59']));

cheers
 
It's in the second line, you can't use "As [Date]" because Date is reserved for Access
 
I've changed the 2nd line to now be:
DateValue([Finished]) AS [FinishDate],

an I'm still getting the same error
 
I believe the problem might be using ( ) as field names.. Not sure though.. Also not sure if you can compare date values with a string i.e. using ' try enclosing them in between # 24/04/2012 00:00:00 #
 
so i need to prefix each field with tablename.[fieldname]?
 
No, I think you are getting a error because some fields have parentheses in them.. like Planned Powder Usage (Kg) and also, Total is yet again a reserved keyword (i think) :???
 
I've got the query to run but had to tweak it a bit, what is the difference between using HAVING and WHERE?
Code:
SELECT 
DATEVALUE([Finished]) AS [FinishDate], 
Paintshop_tblProduction.PartNo, 
Paintshop_tblProduction.Description,
Sum(Paintshop_tblProduction.[Actual Qty Produced]) AS Total, 
Paintshop_tblRoutings.OpDescription, 
Paintshop_tblRoutings.Powder_Usage,
(([Powder_Usage]*Sum([Actual Qty Produced]))/1000) AS [Planned Powder Usage (Kg)],
Paintshop_tblRoutings.QtyPerJig, 
(Sum([Actual Qty Produced])/[QtyPerJig]) AS [Jigs Painted], 
Paintshop_tblRoutings.[Area (m2)] AS [Area per panel (m2)], 
Paintshop_tblRoutings.[Wrap Around (%)], 
([Area (m2)]*(1+(([Wrap Around (%)]/100))/100)*(Sum([Actual Qty Produced]))) AS [Total Area (m2)], 
(Sum([Actual Qty Produced])/[QtyPerJig])*(50/1000) AS [Powder lost to Jigs (Kg)]

FROM Paintshop_tblProduction 
LEFT JOIN Paintshop_tblRoutings ON Paintshop_tblProduction.PartNo = Paintshop_tblRoutings.PartNo

WHERE Paintshop_tblProduction.Finished Between # 24/04/2012 # And # 25/04/2012 #

GROUP BY DateValue([Finished]), 
Paintshop_tblProduction.PartNo, 
Paintshop_tblProduction.Description, 
Paintshop_tblRoutings.OpDescription, 
Paintshop_tblRoutings.Powder_Usage, 
Paintshop_tblRoutings.QtyPerJig, 
Paintshop_tblRoutings.[Area (m2)], 
Paintshop_tblRoutings.[Wrap Around (%)]
This runs ok now
 

Users who are viewing this thread

Back
Top Bottom