VBA/ADO INSERT INTO Statement error...

[SQLBI]

Hi,

I'm trying to execute the following INSERT INTO Statement via ADO, however when i run the code i get an error message stating "Syntax Error in INSERT INTO Statement".

I can't see any problems so any help would be appreciated.

Private Sub cmdDiarySave_Click()

Dim dteDate As Date
Dim strUser As String
Dim lngClient As Long
Dim strText As Variant
Dim strSQL As String

dteDate = Me.txtDiaryDate
strUser = Me.txtDiaryUser
lngClient = Me.txtDiaryClientID
strText = Me.txtDiaryText

strSQL = "INSERT INTO tblDiary (ClientID, UserID, Date, Detail) " & _
          "VALUES(" & lngClient & ", '" & strUser & "', #" & dteDate & "#, '" & strText & "');"

Debug.Print

strSQL ***This looks ok in the immediate window.

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

    cnn.Execute strSQL

***Falls over here

End Sub

Thanks in advance.

Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[zcjl]

Could you try using Format$(dteDate,"d-mmm-yyyy&quot instead of dteDate.

 

[SQLBI]

Found it... It didn't like 'Date' word, reserved and all that! Changed it to DiaryDate and it worked fine.

Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk