Syntax Error (missing operator) in query expression

[RogerD]

the code below works perfectly. When I designed the database, I added 8 extra columns for future use. We now need to use all 8 of the columns that were in the db, but weren't used for anything.
SELECT PartsList.MODELNAME, temp.[model number], PartsList.Paper, PartsList.PcsPerPaper, temp.[production line] AS Line, temp.[date time], temp.[production quantity2], (Abs(([production quantity2]*1000))) AS ProdQty, DateValue([date time]) AS [Date], temp.[special notes], PartsList.MfrModel, PartsList.Box, PartsList.PcsPerBox


FROM temp LEFT JOIN PartsList ON temp.[model number] = PartsList.Post;

when I add the "PartsList.Extra1, PartsList.PcsPer1" to the query as below, I receive the error message, "Syntax Error (missing operator) in query expression 'model number'".
If I remove the added items, the query runs as it should.

SELECT PartsList.MODELNAME, temp.[model number], PartsList.Paper, PartsList.PcsPerPaper, temp.[production line] AS Line, temp.[date time], temp.[production quantity2], (Abs(([production quantity2]*1000))) AS ProdQty, DateValue([date time]) AS [Date], temp.[special notes], PartsList.MfrModel, PartsList.Box, PartsList.PcsPerBox, PartsList.Extra1, PartsList.PcsPer1
FROM temp LEFT JOIN PartsList ON temp.[model number] = PartsList.Post;


Any help is appreciated.

[ranman256]

it may be the parens around the ABS, just use:
Abs([production quantity2])*1000 as ProdQty

[RogerD]

Abs(([production quantity2]*1000)) AS ProdQty
I just tried that
same error message.

When we decided we needed to use the extra columns I had added to the table upon building, I had to change the format of 4 of the fields from short text to number. The number format is the same number format as other items in the table.

I even tried re-writing the query from scratch, removing all existing calculated fields, and still received the same error message.

[RogerD]

I have changed "Model Name" to "ModelName" and "Model Number to "ModelNumber" using the "AS" statement. that appears to have resolved my issue.
why is that?

[JoeM]

Not sure. It should work if you place square brackets around the field name.
That being said, it is usually best practice to NOT use any spaces in your field or object names. It avoids a lot of issues. Many people will use underscores instead of spaces.

[June7]

Also, Date is a reserved word. Should not use reserved words as names for anything.