Converting String to Date

[Scott]

I'm getting a type mismatch below because "myDateVar" is a string type. How
can I convert "myDateVar" to a date type, so my SQL statement will test
coorectly and find a record if it has a dtDate field equal to 5/7/2006?

The t_data table is a sql ntable and dtDate is datetime type.

CODE **********

myDateVar = "20060507" ' string type

Dim objRS2 As ADODB.Recordset

Set objRS2 = New ADODB.Recordset
objRS2.ActiveConnection = CurrentProject.Connection
objRS2.CursorType = adOpenStatic
objRS2.Open "SELECT * from t_data WHERE dtDate = " & "'" & myDateVar &
"'"

 

[Douglas J Steele]

Try putting dashes in there:

myDateVar = "2006-05-07"