Error in Select Statement 2

[nicks60610]

I keep getting this error:

Error: -2147217900
Error Description: [Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.


When I run this select statement in my VB code, I look at the spelling of the tables and they are identical to the code in Access qry, so that's not the issue, but am I missing something else??

Thanks in advance

cmdLookupMachines.CommandText = "SELECT [Production Data Table].[Date], [Production Data Table].[Shift], [Production Data Table].[Line], [Production Data Table].[Pieces], [Production Data Table].[Scrap], Sum(([Pieces]+Nz([Scrap])/2)) AS CycleCount" & _
"FROM [Production Data Table]" & _
"WHERE ([Production Data Table].Date) Between Date() And Date()-1) AND (([Production Data Table].Line)='1A')" & _
"GROUP BY [Production Data Table].[Date], [Production Data Table].[Shift], [Production Data Table].[Line], [Production Data Table].[Pieces], [Production Data Table].[Scrap]"

 

[gmmastros]

You are missing square brackets around your field names in the where clause.

You are also missing a space between As CycleCount and From

In cases like this, I always add a space at the end of EACH line you are concatenating together.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

You are also missing a space before the GROUP BY and a parenthesis, here...

([Production Data Table].Date) Between Date() And [!]([/!]Date()-1)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Andrzejek]

This is very hard to see what's wrong with it:

cmdLookupMachines.CommandText = "SELECT [Production Data Table].[Date], [Production Data Table].[Shift], [Production Data Table].[Line], [Production Data Table].[Pieces], [Production Data Table].[Scrap], Sum(([Pieces]+Nz([Scrap])/2)) AS CycleCount" & _
"FROM [Production Data Table]" & _
"WHERE ([Production Data Table].Date) Between Date() And Date()-1) AND (([Production Data Table].Line)='1A')" & _
"GROUP BY [Production Data Table].[Date], [Production Data Table].[Shift], [Production Data Table].[Line], [Production Data Table].[Pieces], [Production Data Table].[Scrap]"

Try this instead:

[b]Debug.Print[/b] "SELECT [Production Data Table].[Date], [Production Data Table].[Shift], [Production Data Table].[Line], [Production Data Table].[Pieces], [Production Data Table].[Scrap], Sum(([Pieces]+Nz([Scrap])/2)) AS CycleCount" & _
"FROM [Production Data Table]" & _
"WHERE ([Production Data Table].Date) Between Date() And Date()-1) AND (([Production Data Table].Line)='1A')" & _
"GROUP BY [Production Data Table].[Date], [Production Data Table].[Shift], [Production Data Table].[Line], [Production Data Table].[Pieces], [Production Data Table].[Scrap]"

This way you can see what statement goes to your DB.
Also, you can copy it from your Immediate Window and try it outside your VB app.

Have fun.

---- Andy

 

[nicks60610]

Excellent, that got me a lot further (new errors, but further) , thanks guys!!

 

[gmmastros]

On problem you will eventually face is with the Group By clause.

A field you are returning is: Sum((Pieces+Nz(Scrap)/2))
But in your group by, you have pieces and Scrap, so... each record will be returned instead of a sum amongst the records.

I suggest you remove the pieces and scrap fields from the select clause and also remove them from the group by clause.

By doing so, you should get 1 record for each Date, Shift, and Line combination.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nicks60610]

George, you must have been reading my mind, that is what I was going to figure out next, thanks!!