SQL Syntax Error

[burrina]

Don't know what I am missing here? The where line is the issue!

 Dim SSql As String
  
SSql = "UPDATE tblHour" _
         & " SET tblHour.WorkDate =#" & Me.AbsenceHrsID  " _
         & " WHERE tblHour.EmployeeID= " & Me.EmployeeID

CurrentDb.Execute SSql, dbFailOnError

 

[jdraw]

What error are you receiving?
Suggest you always do a DEBUG.PRINT SSql before trying to execute.

What data type is Me.AbsenceHrsID? Seems like a number, so no quote and no # required.

you only have 1 # if you are trying to encapsulate a date/Time data type.

 

[burrina]

Yes, it is a number.Revised Code,Error is missing operator in AbsenceHrsID

SSql = "UPDATE tblHour" _
         & " SET tblHour.WorkDate = & Me.AbsenceHrsID  " _
         & " WHERE tblHour.EmployeeID= " & Me.EmployeeID

 

[jdraw]

Try this - I moved the quote. You want to get the value from Me.AbsenceHrsID

SSql = "UPDATE tblHour" _
         & " SET tblHour.WorkDate =" & Me.AbsenceHrsID   _
         & " WHERE tblHour.EmployeeID= " & Me.EmployeeID

Try the debug.print ssql

 

[burrina]

Had to revise code, it was not what I wanted. Error is:
Error 3075 syntax error in query expression '#5/13/20154 WHERE tblHour.EmployeeID=2'.)

 Dim strSQL As String
                
strSQL = "INSERT INTO tblHour (WorkDate,Hours) "
strSQL = strSQL & "VALUES (#" & Me.HolidayDate & Me.AbsenceHrsID _
     & " WHERE tblHour.EmployeeID= " & Me.EmployeeID
        CurrentDb.Execute strSQL, dbFailOnError

 

[jdraw]

Hours may be a reserved word in Access???? I know HOUR is.

Dim strSQL As String
                
strSQL = "INSERT INTO tblHour (WorkDate,Hours) "
strSQL = strSQL & "VALUES (#" & Me.HolidayDate & "#," & Me.AbsenceHrsID  & ")" _
     & " WHERE tblHour.EmployeeID= " & Me.EmployeeID
debug.print strSQL 
        CurrentDb.Execute strSQL, dbFailOnError

 

[burrina]

Hmm, so far no issues with that field name!
Still, I get this error.

Error 3075 syntax error in (missing operator) query expression '4 WHERE
tblHour.EmployeeID=2'.)

 

[jdraw]

I just wrote a small routine to test it. Since I don't have your form, I had to supply some variables

Sub testzz()
Dim strSQL As String
Dim Holidaydate As Date
Dim AbsenceHrsID As Long
Dim EmployeeID As Long
Holidaydate = #23/05/2015#
EmployeeID = 899
AbsenceHrsID = 7777
strSQL = "INSERT INTO tblHour (WorkDate,Hours) "
strSQL = strSQL & "VALUES (#" & Holidaydate & "#," & AbsenceHrsID & ")" _
     & " WHERE tblHour.EmployeeID= " & EmployeeID
Debug.Print strSQL
End Sub

and it gives as result

INSERT INTO tblHour (WorkDate,Hours) VALUES (#23/05/2015#,7777) WHERE tblHour.EmployeeID= 899

 

[WayneRyan]

You can't use a Values and a Where clause together.

Wayne

 

[burrina]

Maybe that's the problem. The solution escapes me for now. I have tried this with a popup form and also a subform with no luck. Of course it has to a matching value for the Employee. EmployeeID to EmployeeID in popup form
Or EmployeeID to AbsenceTypeID in subform.

I will continue to test and post back.

 

[Galaxiom]

Maybe that's the problem.

Of course it is the problem.

Inserting records adds them to the table. A Where clause is clearly meaningless in this context.

If you want to add records then you need to include the EmployeeID field in the inserted values.

 

[jdraw]

Duh!!!
Of course - thanks Wayne. I was intent on solving the syntax errors, I missed the Insert with Values and Where.

Sorry burrina for leading you astray, but you did have syntax issues with the quotes etc.

 

[burrina]

No Problem, Still struggling with it. Latest Attempt. OnLoadEvent of form.
No errors when form opens? Debug shows code in red, syntax error is all.

Dim strSQL As String
Dim HolidayDate As Date
Dim AbsenceHrsID As Long
Dim EmployeeID As Long
Dim HolDay As Parameter
                
strSQL = "INSERT INTO tblHour (WorkDate,Hours,HolDay) "
strSQL = (" & Me.HolidayDate & "" & Me.AbsenceHrsID & "#,True,#" _
     & " WHERE tblHour.EmployeeID= " & Me.EmployeeID
Debug.Print strSQL, dbFailOnError
        CurrentDb.Execute strSQL, dbFailOnError

 

[jdraw]

As Wayne and Galaxiom have posted:
You can not use a Where clause with an Insert when using VALUES

If you want these values to be associated with Me.EmployeeID, then you would use

Dim strSQL As String
Dim HolidayDate As Date
Dim AbsenceHrsID As Long
Dim EmployeeID As Long
Dim HolDay As Boolean   ' can be True or False
                
strSQL = "INSERT INTO tblHour (WorkDate,Hours,HolDay,[COLOR="DarkRed"][B]EmployeeID[/B][/COLOR]) " _
          & " VALUES  (#" & Me.HolidayDate & "#," & Me.AbsenceHrsID & ",True,"  & Me.EmployeeID &")"
Debug.Print strSQL, dbFailOnError
        CurrentDb.Execute strSQL, dbFailOnError

I don't know why you dimmed HolDay as "Parameter"????

Perhaps you should tell us again in plain English, WHAT you are trying to accomplish.

The code above will:
INSERT a new record in tblHour with
WorkDate valued with Me.HolidayDate
Hours valued with Me.AbsenceHrsID
HolDay valued with TRUE
EmployeeID valued with Me.EmployeeID

If that isn't what you intended , then we need more info.

 

[burrina]

Thank You. That Worked is exactly what I needed.
Thanks Everyone for sticking with me.

 

[jdraw]

Happy to help.