Inserting Date syntax

[gmazza via AccessMonster.com]

Hi there,
I am having a problem inserting a Date field into a table, keep getting a
syntax error, any ideas, I've been trying and can't quite figure it out.

sSql = "INSERT INTO PatientData ( PatientId, ClinicalTrialId, VisitDate)
" & _
"VALUES(" & _
"'" & Me.PatientId & "', " & _
"'" & GetActiveStudy() & "', " &
_
"'" & Format(Me.VisitDate, "\#mm\
/dd\/yyyy\#") & ");"

 

[Jeff Boyce]

Why are you using the Format() function on your date field? That is, what
do you want it to do?

I thought you just wanted the date in that date field to get inserted into a
date/time-datatype field...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

Hi there,
I am having a problem inserting a Date field into a table, keep getting a
syntax error, any ideas, I've been trying and can't quite figure it out.

sSql = "INSERT INTO PatientData ( PatientId, ClinicalTrialId, VisitDate)
" & _
"VALUES(" & _
"'" & Me.PatientId & "', " & _
"'" & GetActiveStudy() & "', " &
_
"'" & Format(Me.VisitDate, "\#mm\
/dd\/yyyy\#") & ");"

What you have is fine except you put an extra "'" in fornt
of Format

If your PatientId and ClinicalTrialId fields are number type
fields, then you have the same problem (along with an extra
' before the comma) with these fields.