Syntax Error in INSERT INTO - but I just cant see what it is? (1 Viewer)

shabbaranks

Registered User.
Local time
Today, 22:52
Joined
Oct 17, 2011
Messages
300
Hi,

I cant seem to get my insert statement to work, Ive checked (although I could be wrong) for reserved words and I don't seem to have any. Ive declared what the fields are so that should be ok, it just wont insert:

Code:
Dim sEmployeeName As String
Dim sLogonAccount As String
Dim sDepartment As String
Dim sProject As String
Dim sActivity As String
Dim sDetail As String
Dim MySQL As String
Dim sDate As Date
Dim sTime As Double
 
If Me.Project = "" Then
    MsgBox "You must enter a Project before continuing", vbInformation
    Me.Project.SetFocus
    Exit Sub
End If
If Me.Activity = "" Then
    MsgBox "You must select an Activity before continuing", vbInformation
    Me.DatePicker.SetFocus
    Exit Sub
End If

If Me.DatePicker = "" Then
    MsgBox "Have you selected a date?", vbInformation
    Me.DatePicker.SetFocus
    Exit Sub
End If
 
Me.Description = Replace(Me.Description, "'", "")
Me.Description = Replace(Me.Description, ",", "")
Me.Description = Replace(Me.Description, """", "")
sEmployeeName = Me.FulName_txtbx
sLogonAccount = Me.LoginAcc_txtbx
sDepartment = Me.Department_txtbx
sProject = Me.Project
sActivity = Me.Activity
sDate = Me.DatePicker
sTime = Me.Hour & "" & Me.MinutesCombo.Column(1)
sDetail = Me.Description

MySQL = "INSERT INTO TimesheetTableTemp (Employee, [Logon Account], [User Department], [Project Number], Activity, [Task Date], Hours, Detail) Values "
MySQL = MySQL & "('" & sEmployeeName & "', '" & sLogonAccount & "', '" & sDepartment & "', '" & sProject & "', '" & sActivity & "', '" & DateValue(sDate) & "', '" & sTime & "', '" & sDescription & "', )"
DoCmd.SetWarnings True
DoCmd.RunSQL MySQL
DoCmd.SetWarnings False
Me!TimesheetTableSubform.Form.Requery
ClearField
Me.TotalAccumHours_txtbx.Requery

Any ideas?
 

pr2-eugin

Super Moderator
Local time
Today, 22:52
Joined
Nov 30, 2011
Messages
8,494
Dates need to be surrounded by #, not '. Double/Numbers do not need any special enclosures. Also there is a dangling , at the end.
Code:
MySQL = "INSERT INTO TimesheetTableTemp (Employee, [Logon Account], [User Department], [Project Number], Activity, [Task Date], Hours, Detail) Values "
MySQL = MySQL & "('" & sEmployeeName & "', '" & sLogonAccount & "', '" & sDepartment & "', '" & sProject & "', '" & sActivity & "', [COLOR=Red][B]#[/B][/COLOR]" & DateValue(sDate) & "[COLOR=Red][B]#, "[/B][/COLOR] & sTime & [COLOR=Red][B]", '"[/B][/COLOR] & sDescription & [COLOR=Red][B]"')"[/B][/COLOR]
 

shabbaranks

Registered User.
Local time
Today, 22:52
Joined
Oct 17, 2011
Messages
300
Easy when you know how :) thank you
 

Users who are viewing this thread

Top Bottom