Insert into sql statement (1 Viewer)

[access7]

Good Morning

I am hoping someone can help me locate where this error may be... I am getting a syntax error when I try to execute... it has not given me an error when writing the code and it steps through both lines fine with all values OK when viewing in the immediate window. Then when it steps down to the execute it tells me there is a syntax error in the INSERT INTO statement... I cannot see where

Here is my code...

Sub InsertCorrespondence()

lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_Correspondence ( DiaryActionID, CompanyRef, CorrType, Contact, Date, Time, Notes, CreatedBy, CreatedWhen ) "
lsSQL = lsSQL & " VALUES ( '" & txtDiaryActionID.Value & "', " & ICompanyRef & ", '" & lstCorrType.Value & "', '" & CboContact.Value & "', '" & Date & "', '" & txtTime.Value & "','" & txtNotes.Value & "', " & SUser & ", " & Now() & ")"

CurrentDb.Execute lsSQL

End Sub

 

[VilaRestal]

In Access SQL dates need to be enclosed in hashes:

lsSQL = lsSQL & " VALUES ( '" & txtDiaryActionID.Value & "', " & ICompanyRef & ", '" & lstCorrType.Value & "', '" & CboContact.Value & "', '" & Date & "', '" & txtTime.Value & "','" & txtNotes.Value & "', " & SUser & ", #" & Now() & "#)"

Apart from that, it looks alright to me.

It's a good idea to

debug.print lsSQL

before the Execute line and then copy what it prints (in the immediate window) into the SQL view of a blank query and try and run it. Access should give a more helpful error then.

 

[access7]

Thanks, I have put the hash signs around the Now(), I also put the hashes around the date and time as on reflection I had them as strings and in the table they are date/time fields - not sure if that makes a difference... I have pasted a debug.print into a blank query (great tip). I am however still getting the syntax error as before... the debug.print now looks like this;

INSERT INTO Tbl_Correspondence ( DiaryActionID, CompanyRef, CorrType, Contact, Date, Time, Notes, CreatedBy, CreatedWhen ) VALUES ( '201', 56, 'Called Client', 'Ray Locke', #27/04/2012#, #09:51#,'test', Natalie H, #27/04/2012 09:51:18#)

I'm really not sure where I am going wrong??

 

[access7]

Ok, I think my first issue was that my table fields were called 'Date' and 'Time', access didnt like this - I have now changed these to 'DateofCorr' and 'TimeofCorr'. I am still getting a syntax error but this time much more specific - it now says;
'Syntax error (missing operator) in query expression 'Ian H' - this is the value I am passing to the CreatedBy field - it is set by a global variable SUser which returns the name of the person logged in

 

[access7]

I had not put that as a string... I needed an extra pair of ''
YEY! It works!
Thanks for your time

 

[VilaRestal]

you're welcome and thanks for updating the thread with your solution