|
Home > Archive > MS Access project with SQL Server > March 2006 > New INSERT syntax problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
New INSERT syntax problem
|
|
| AkAlan via AccessMonster.com 2006-03-06, 7:30 pm |
| 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
--
Message posted via http://www.webservertalk.com
| |
| AkAlan via AccessMonster.com 2006-03-06, 7:30 pm |
| 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 & "'" & ")"
AkAlan wrote:
> 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
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200603/1
|
|
|
|
|