SQL to VBA 2

[Apollo13a]

Hello, I been trying to convert this sql to VBA but I keep getting an error "Syntax error in date query expression."

Set rsDays = CurDB.OpenRecordset( _
"SELECT tblhours.LaborDate, tblhours.tblEmployeeID, DatePart(#yyyy#,[labordate]) AS Expr1, tblhours.LaborHoursST" & _
" FROM tblHours" & _
" GROUP BY tblhours.LaborDate, tblhours.tblEmployeeID, DatePart(#yyyy#,[labordate]), tblhours.LaborHoursST" & _
"HAVING (((DatePart(#yyyy#,[labordate]))=DatePart(#yyyy#,Date())) AND ((tblhours.LaborHoursST)>0));")

Can anyone help me on what syntax I need around the #yyyy# ?
Do I need to place anything around the field [Labordate]?

Thanks,
Jim

 

[Zion7]

Replace the Hash(#) marks, with double quotes.

 

[RoyVidar]

Single quotes, or double up with double quotes

[tt]...DatePart('yyyy',[labordate])...
...DatePart(""yyyy"",[labordate])...[/tt]

Roy-Vidar

 

[Zion7]

uh, that's right, it is vba isn't it!

 

[Apollo13a]

Thanks to both of you. Works great, although I have added a parameter to my query and want to pass the value thru from my function call. I am still having a little trouble with the syntax.I'm getting an error on the line that starts with "Having

Set rsDays = CurDB.OpenRecordset( _
"SELECT tblhours.LaborDate, tblhours.tblEmployeeID, DatePart('yyyy',[labordate]) AS Expr1, tblhours.LaborHoursST" & _
" FROM tblHours" & _
" GROUP BY tblhours.LaborDate, tblhours.tblEmployeeID, DatePart('yyyy',[labordate]), tblhours.LaborHoursST" & _
" HAVING (((tblHours.tblEmployeeID) = " & ID & " ) And(((DatePart('yyyy',[labordate]))=DatePart('yyyy',Date())) AND ((tblhours.LaborHoursST)>0));")

Thanks Jim

 

[Zion7]

I really don't know, but I'll suggest this...

Set rsDays = CurDB.OpenRecordset( _
"SELECT LaborDate, tblEmployeeID, DatePart('yyyy',[labordate]) AS Expr1, LaborHoursST" & _
" FROM tblHours" & _
" GROUP BY LaborDate, tblEmployeeID, DatePart('yyyy',[labordate]), LaborHoursST" & _
" HAVING ((tblEmployeeID = " & ID & " ) And(((DatePart('yyyy',[labordate]))=DatePart('yyyy',Date())) AND ((LaborHoursST)>0));")

BTW, all the table references are not necessary, unless joining tables and 2 fields have same name.

 

[Apollo13a]

Thanks Zion7
You pointed me in the right direction. The error was in the number of parenthesis.

" HAVING tblEmployeeID = " & ID & "  And (DatePart('yyyy',[labordate]) = DatePart('yyyy',Date()) AND ((LaborHoursST)>0));")

Thanks for the info on the references, certainly helps clean it up.
Thanks