run-time error 13 type mismatch (1 Viewer)

[Andy Tag]

:banghead:

Good Morning I am new at Access/VBA and this forum
I get a run-time error 13 type mismatch error when I Dim OriginationDate As Date. Code runs fine when Dim OriginationDate As String

Any ideas?

'Option Explicit

Dim KRFcon As ADODB.Connection
Dim KRFrs As ADODB.Recordset
Dim vrefnum As Double
Dim KRFsCon, KRFsSQL, vTable, datacol, L, datavalue As String
Dim Author As String
Dim Area As String
Dim Comments1 As String
Dim id As String
Dim Newid As String
Dim OriginationDate As Date




' Open a connection.
Set KRFcon = New ADODB.Connection
KRFsCon = "Provider=SQLOLEDB;Data Source=kpesmnsql201;Initial Catalog=KRFSiteServices;Integrated Security=SSPI"
'MsgBox (DRsCon)
KRFcon.Open KRFsCon
Set KRFrs = New ADODB.Recordset



' Make a query over the connection and find the last ID add one to create the new RecordID

KRFsSQL = "select top 1 [ID] from krfdatabase order by [id] DESC"
KRFrs.Open KRFsSQL, KRFcon, adOpenStatic, adLockBatchOptimistic, adCmdText
currentID = KRFrs.Fields(0).Value
Newid = currentID + 1
KRFrs.Close

' Date service request is made

OriginationDate = Format$(Now(), "mm/dd/yyyy hh:mm")

MsgBox (OriginationDate)

'This populates all data from form to SQL
KRFsSQL = "insert into krfdatabase ([Author], [Area], [comments1], [OriginationDate]"
KRFsSQL = KRFsSQL + ") VALUES (" + "'" + Author + "'" + "," + "'" + Area + "'" + "," + "'" + Comments1 + "'" + "," + "'" + OriginationDate + "'"
KRFsSQL = KRFsSQL + ")"
MsgBox "Record #" + [Newid] + " was created"
KRFrs.Open KRFsSQL, KRFcon, adOpenKeyset, adLockBatchOptimistic, adCmdText 'update a record

 

[namliam]

OriginationDate = Format$(Now(), "mm/dd/yyyy hh:mm")

You format it as string and wonder why you get an error ??

 

[VBprogrammer]

Try DateTime instead of Date.

Thanks,
VBProgrammer

 

[Andy Tag]

OriginationDate = Format$(Now(), mm/dd/yyyy hh:mm) any suggestions on date format?

 

[namliam]

You are reformatting NOW which is a date into a string, now you want it back into a date?

Simply use NOW instead of the whole format

 

[Andy Tag]

Msgbox returned date time Now(). However still received run time error 13 type mismatch.

 

[Andy Tag]

Still received a run time error 13 type mismatch.

Dim OriginationDate As Date

OriginationDate = Now


MsgBox (OriginationDate)

'This populates all data from form to SQL
KRFsSQL = "insert into krfdatabase ([Author], [Area], [comments1], [OriginationDate]"
KRFsSQL = KRFsSQL + ") VALUES (" + "'" + Author + "'" + "," + "'" + Area + "'" + "," + "'" + Comments1 + "'" + "," + "'" + OriginationDate + "'"
KRFsSQL = KRFsSQL + ")"
MsgBox "Record #" + [Newid] + " was created"
KRFrs.Open KRFsSQL, KRFcon, adOpenKeyset, adLockBatchOptimistic, adCmdText 'update a record

 

[Andy Tag]

Thanks Namliam, I made the change, however I still receive the run-time error. Script stops at the populates SQL step. SQL OriginationDate field is set up as date/time. VBA doesn't have Datetime only Date.

Dim OriginationDate As Date
' Date service request is made

OriginationDate = Now
MsgBox (OriginationDate)

'This populates all data from form to SQL
KRFsSQL = "insert into krfdatabase ([Author], [Area], [comments1], [OriginationDate]"
KRFsSQL = KRFsSQL + ") VALUES (" + "'" + Author + "'" + "," + "'" + Area + "'" + "," + "'" + Comments1 + "'" + "," + "'" + OriginationDate + "'"
KRFsSQL = KRFsSQL + ")"
MsgBox "Record #" + [Newid] + " was created"
KRFrs.Open KRFsSQL, KRFcon, adOpenKeyset, adLockBatchOptimistic, adCmdText 'update a record

 

[namliam]

That error will not be in this part

Dim OriginationDate As Date

OriginationDate = Now


MsgBox (OriginationDate)

But will rather be later on...
Either when building the string

KRFsSQL = KRFsSQL + ") VALUES (" + "'" + Author + "'" + "," + "'" + Area + "'" + "," + "'" + Comments1 + "'" + "," + "'" + OriginationDate + "'"

Try using & instead of +

Or if it fails at inserting into the date values, try replacing the ' around Originationdate (again making it a string) by #
Just FYI, you should really keep

OriginationDate = Format$(Now(), mm/dd/yyyy hh:mm)

Since Access sql requires US formats

 

[Andy Tag]

replacing + with & worked. Thanks!

 

[MSAccessRookie]

replacing + with & worked. Thanks!

In case you were wondering why the change worked, and if you should always use "&", I would imagine that each has their own place, but in this case, the difference probably was in the Instance where part of the equation was NULL.

"Hello " + NULL = NULL

"Hello " & NULL = "Hello "

-- Rookie

 

[pbaldy]

FYI, thread moved from watercooler.

 

[Andy Tag]

I see! Thanks for the feedback.