vba sql datetime format

[Thom]

Hi all.

A database is being tinkered with and we needed a few columns and the data.

So I saved it down to Access and then Excel.

three columns
DateEntered
Date_Expiry
PhotoURL

first two are a datetime format in sql.
third is a varchar.

So now I need to write this back into a database. I have setup a blank db to test on. This is my code:

[vba]
Dim row As Integer
Dim strSQL As String
For row = 2 To 3382
strSQL = "insert into tblTest (DateEntered, DateExpiry, PhotoURL) values ('" & XLSheet.Cells(row, 3).Value & "','" & XLSheet.Cells(row, 4).Value & "','" & XLSheet.Cells(row, 5).Value & "')"
'MsgBox (strSQL)
con.Execute strSQL
Next row

[/vba]
If I set the three columns in SQL as varchar or char, it works fine.

But I need the dates in datetime format. They are formatted correctly in Excel (eg: 01/02/2008)

This is the error I'm getting:

[vba]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
[/vba]
So I tried without quote marks in the vba but I get a syntax error.

Ideas?

Thanks

update -

Actually I'll explain a little more as I've noticed a few things.

Not all the fields have a datestamp on them or a PhotoURL.

It seems to error at line 7. I thought it might be to do with NULLs but that isn't the case. In fact, in my SQL, where the column should be NULL or blank, it has the date - 01/01/1900 in it!

So I don't want that, but I am still getting the above error, even though it is entering the data

[Oorang]

Try (untested):
[vba]
Sub Example()
Dim row As Integer
Dim strSQL As String
For row = 2 To 3382
strSQL = "insert into tblTest (DateEntered, DateExpiry, PhotoURL) values ('" & _
Format$(XLSheet.Cells(row, 3).Value, "YYYYMMDD") & "','" & _
Format$(XLSheet.Cells(row, 4).Value, "YYYYMMDD") & "','" & _
XLSheet.Cells(row, 5).Value & "')"
'MsgBox (strSQL)
con.Execute strSQL
Next row
End Sub
[/vba]

[Thom]

Excellent, that went through without erroring

However, it is still inserting in 01/01/1900 when it should be blank.

Is that an excel thing or an SQL thing?

[Oorang]

It's probably Excel. Make sure the field is set to allow nulls then use this:
[VBA]Public Sub Example()
Dim row As Integer
Dim strSQL As String
For row = 2 To 3382
strSQL = "insert into tblTest (DateEntered, DateExpiry, PhotoURL) values (" & _
CleanValue(XLSheet.Cells(row, 3).value) & "," & _
CleanValue(XLSheet.Cells(row, 4).value) & ",'" & _
XLSheet.Cells(row, 5).value & "')"
'MsgBox (strSQL)
con.Execute strSQL
Next row
End Sub
Private Function CleanValue(ByVal value As String) As String
Dim strRtnVal As String
strRtnVal = Trim$(value)
If Val(strRtnVal) = 0# Or LenB(value) = 0 Then
strRtnVal = "NULL"
Else
strRtnVal = Format$(XLSheet.Cells(row, 3).value, "'YYYYMMDD'")
End If
CleanValue = strRtnVal
End Function
[/VBA]