update table in access from excel using sql

The error is specific to the update statement

What Izandol said. Dates must be within # symbols and strings must be within ' symbols.

Hi, thanks for the replies. I adjusted the fileds and even commented out all but the date and am still getting a syntax error in the update statement. Could it be how I am refernceing the tables?

Sub test()

Dim acApp As Object
Dim sqlString As String
Dim dbs As Database
Dim ws As Worksheet
Dim wsn As String
Dim c As Range
Dim tn As String
Dim rng As Range

wsn = Range("wsName").Value
tn = Range("tblName").Value

Set acApp = CreateObject("Access.Application")
Set rng = Application.Range(tn & "[completed]")


Worksheets(wsn).Activate

For Each c In rng
If c.Value = True Then
sqlString = "UPDATE tbl_RETRO_EFF_76_PREV_WK AS t1 INNERJOIN tablec AS t2 ON t1.HIC_NUMBER = t2.HIC_NUMBER set"
sqlString = sqlString & " [COMPLETED] =" & c.Value & ","
sqlString = sqlString & " [DATE] = #" & c.Offset(0, -1).Value & "#,"
sqlString = sqlString & " [NTID] '=" & c.Offset(0, -2).Value & "';"

Debug.Print sqlString


With acApp
Set dbs = OpenDatabase("mydb")
With dbs
.Execute sqlString, dbFailOnError
.Close
End With
Set dbs = Nothing
End With
Else
End If

Next

'tablec is the excel table and tbl_RETRO_EFF_76_PREV_WK is the access table

Thanks again

here's the sql statement - UPDATE tbl_RETRO_EFF_76_PREV_WK AS t1 INNERJOIN [tablec] AS t2 ON t1.HIC_NUMBER = t2.HIC_NUMBER set [COMPLETED] =True, [DATE] = #3/17/2014#, [NTID] ='Mike';

I believe it's an issue with the date. I changed a few things and added some variables. All of them work aside from the date variable. I used the ntid and completed together and it updates fine, but as soon as I add the date, it fails.

sqlString = "UPDATE " & qn & " AS t set NTID = '" & z & "', COMPLETED = " & x & ", DATE = #" & y & "# WHERE HIC_NUMBER = '" & h & "'"

here's the debug print - UPDATE tbl_RETRO_EFF_76_PREV_WK AS t set NTID = 'chuck', COMPLETED = True, DATE = #3/17/2014# WHERE HIC_NUMBER = '1234ABC'

**The columns that use dates are formatted as dates and the y variable is a date.**

Thanks in advance!

It did the same thing. I changed the field DATE to COMPLETED_DATE and it worked fine. Any idea why DATE wouldn't work in updating data from excel to access?

ahh that makes sense, thanks for all the help!