vba query syntax problem

[clifftech]

I would appreciate help on figuring out what is wrong with the below vba query. The error message I am getting is "Too few parameters. Expected1." I set up a watch on the query and provided the watch results below also. I have tried many different syntax combinations on the WHERE clause and get other syntax error messages.


VBA query:
strSql = "UPDATE tblGrid SET " & varMonth & " = " & varHours & " WHERE (((tblGrid.SubAccount) = '" & varSubAccount & "'));"


Watch result:
"UPDATE tblGrid SET Month1Hrs = 11 WHERE (((tblGrid.SubAccount) = 'D6303.0.0.0.0'));"

 

[jedraw]

Is Month1Hrs a field in your table tblGrid?

 

[clifftech]

Month1Hrs is a field in tblGrid but when you asked the question I found that the field is actually misspelled - it should be MonthHrs1! Thanks for your help.

 

[clifftech]

jedraw - Here's a much more difficult vba query that is giving me different errors. One of the errors is due to the "m" parameter in the DateAdd function. Can you help?

strSql = "SELECT * FROM tblEmployee_ProjectHours "
strSql = strSql & " WHERE (((tblEmployee_ProjectHours.MonthYear) >= [Forms]![frmProjectHours]![cboBegMonth]))"
strSql = strSql & " And (((tblEmployee_ProjectHours.MonthYear)<=DateAdd("m",11,[Forms]![frmProjectHours]![cboBegMonth])));"

 

[strongm]

strSql = strSql & " And (((tblEmployee_ProjectHours.MonthYear)<=DateAdd([red]"[/red]"m"[red]"[/red],11,[Forms]![frmProjectHours]![cboBegMonth])));"

 

[clifftech]

That works! I didn't know you could use that syntax. Is there any website or document that lists all the syntax options for vba queries?

 

[strongm]

That's nothing to do with the query, it is simply to do with the way VBA parses strings, and the fact that without Option Explicit being set (I anticipate) m is being treated as an unitialised variable in your version. i.e. if you try

MsgBox " And (((tblEmployee_ProjectHours.MonthYear)<=DateAdd("m",11,[Forms]![frmProjectHours]![cboBegMonth])));"

I'd expect that you'll see:

And (((tblEmployee_ProjectHours.MonthYear)<=DateAdd(,11,[Forms]![frmProjectHours]![cboBegMonth])));

To see what is happening, try:
m=5
MsgBox " And (((tblEmployee_ProjectHours.MonthYear)<=DateAdd("m",11,[Forms]![frmProjectHours]![cboBegMonth])));"

and finally:
m="m"
MsgBox " And (((tblEmployee_ProjectHours.MonthYear)<=DateAdd("m",11,[Forms]![frmProjectHours]![cboBegMonth])));"

 

[PHV]

I'd expect a compile error ...

 

[strongm]

Me too. It is print and debug.print that work the way I mention. Schoolboy error me then moving to a MSgBox to illustrate it. Still it's Xmas, and I'm mostly thinking about drinks parties ...

 

[PHV]

I agree: merry Xmas

 

[clifftech]

So it's back to work after having a great Christmas and eating too much and starting the day off with another syntax problem. I'm getting either a syntax error or missing item in query error. ProjectID and getDate are strings and the query is a single line of code. Any suggestions?

strSql = "UPDATE " & dataTable & " SET Hours = " & plannedHours & " WHERE (((EmpID) = " & employeeID & ") And ((SubAccount) = """ & ProjectID & """) AND ((MonthYear) = #" & getDate & "#)"

 

[Andrzejek]

Show what you get when you do:
[tt]
debug.print strSql
[/tt]

Have fun.

---- Andy

 

[clifftech]

not sure how to debug.print works but when I add strSql to watch expression I get

"UPDATE tblEmployee_ProjectHours SET Hours = 100 WHERE (((EmpID) = 11102) And ((SubAccount) = "D6108.M.2.0.0") AND ((MonthYear) = #01/01/2013#)

 

[Andrzejek]

not sure how to debug.print works"
Add it to your code like this:
[tt]
strSql = "UPDATE " & dataTable & " SET Hours = " & plannedHours & " WHERE (((EmpID) = " & employeeID & ") And ((SubAccount) = """ & ProjectID & """) AND ((MonthYear) = #" & getDate & "#)"

debug.print strSql
[/tt]
And if you put a breake on the Debug line, you get in Immediate Window your SQL

I assume your Hours and EmpID are numbers

You have " (double quotes) around your SubAccount, try to use just ' (single quotes) instead (if your field is a text field) so your SQL will look like:
[tt]
UPDATE tblEmployee_ProjectHours SET Hours = 100 WHERE (((EmpID) = 11102) And ((SubAccount) = [red]'[/red]D6108.M.2.0.0[red]'[/red]) AND ((MonthYear) = #01/01/2013#)

[/tt]

Have fun.

---- Andy

 

[Andrzejek]

Missed that - you have too many ( you don't need any of them:
[tt]
UPDATE tblEmployee_ProjectHours
SET Hours = 100
WHERE EmpID = 11102
And SubAccount = 'D6108.M.2.0.0'
AND MonthYear = #01/01/2013#[/tt]




Have fun.

---- Andy

 

[clifftech]

Found the extra ( Thanks.