New INSERT syntax problem

[AkAlan via AccessMonster.com]

I need a date insererted in the ESR_OPEN_DATE column when I run this code
but I get 12:00 AM instead. The variable is loaded with a valid date. I have
tried every kind of formatting and syntax alteration I can think of and found
posted but I cannot get this to work. I am going to spend all weekend working
this so if anyone can help, please do. I suppose the time field will be an
issue once I can get the date to work so if there is a difference between how
to format a date and a time please show both.

Thanks to all that reply, you guys are great!!. I'm hoping that I can start
answering some of these posts myself someday soon. Just getting started using
SQL from Access.


strJcn = "'" & NextJCN() & "'"
strParcTag = "'" & Me.cboEquipID.Column(0) & "'"
strPerfWc = "'" & Me.cboPWC & "'"
strRptby = "'" & Me.cboReportedBy.Column(0) & "'"
strDisc = "'" & Me.txtDisc & "'"
datOpenDate = Me.txtOpenDate
datOpenTime = Me.txtOpenTime
intCond = Me.cboCond
intStatus = Me.cboStatus
intAction = Nz(Me.cboActionTaken, 0)


strSQL = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG,ESR_DISC,ESR_RPTBY,
ESR_OPEN_DATE)" _
& " VALUES (" & strJcn & "," & strParcTag & "," & strDisc & "" _
& "," & strRptby & "," & datOpenDate & ")"

DoCmd.RunSQL strSQL

 

[AkAlan via AccessMonster.com]

Well I figured it out not 10 minutes after I posted this. I swear I tried for
hours to make this work before posting. Anyway I will post the two lines that
I changed to make it work incase someone else might benefit from it.

just made the variable declaration simple
datOpenDate = Me.txtOpenDate

then added the single quotes inside the double quotes for the datOpendate and
time vars.

strSQL = "INSERT INTO ESR (ESR_JCN,ESR_PARCTAG,ESR_DISC,ESR_RPTBY,
ESR_PERF_WK_CTR,ESR_OPEN_DATE, " _
& " ESR_OPEN_TIME )" _
& " VALUES (" & strJcn & "," & strParcTag & "," & strDisc & "," &
strRptby & "," & strPerfWc & "" _
& "," & "'" & datOpenDate & "'" & "," & "'" & datOpenTime & "'" & ")"