vb to access date problem

some one pls help me

when i try to run the below line it generates an error like 'datatype mismathch in criteria expression'

i new to programming..so pls help me

the line goes like this

"select * from mytable where date between '" & Format(DTPicker2.Value, "mm/dd/yyyy") & "' and '" & Format(DTPicker3.Value, "mm/dd/yyyy") & "'"

i need a reply urgently

Date is a reserved word place it in square brackets. Or better yet change the field name

"select * from mytable where date between '#" & Format(DTPicker2.Value, "mm/dd/yyyy") & "#' and '#" & Format(DTPicker3.Value, "mm/dd/yyyy") & "#'"

Quote:

---

Originally Posted by GaryMazzone

Date is a reserved word place it in square brackets. Or better yet change the field name

---

"select * from mytable where [date] >= #" & Format(DTPicker2.Value, "mm/dd/yyyy") & "# and [date] <= #" & Format(DTPicker3.Value, "mm/dd/yyyy") & "#"

You don't use single quotes AND hash symbols to delimit dates. In SQL Server you delimit date literals with single quotes so they are interpreted as text literals and converted to dates as required. In Access you delimit a date literal with has symbols and only hash symbols. Single quotes are used to delimit text literals and Access will not automatically convert from text to date. That's why the data type mismatch: dates are expected and you're providing text.

That said, you should be using string concatenation like that anyway. Whether you're using VB6 or VB.NET, you should be using parameters to insert your date values into the SQL code. The FAQ forum at the top of this forum provides links to resources that will show you why and how.

Quote:

---

Originally Posted by jmcilhinney

you should be using parameters to insert your date values into the SQL code

---

Code:

---

Private Sub Command1_Click()
On Error GoTo err1

Dim strSQL As String
strSQL = "SELECT * from mytable where [date] >= @param1 AND [date] <= @param2"

Dim cnn As ADODB.Connection: Set cnn = New ADODB.Connection
Dim cmm As ADODB.Command:    Set cmm = New ADODB.Command
Dim rst As ADODB.Recordset:  Set rst = New ADODB.Recordset

With cnn
    .Provider = "Microsoft.jet.oledb.4.0"
    .Properties("Data Source") = App.Path & "\emp2000.mdb"
    .Properties("User ID").Value = "Admin"
    .Properties("Password").Value = ""
    .Open
    If .State = adStateOpen Then
        With cmm
            .ActiveConnection = cnn
            .CommandType = adCmdText
            .CommandText = strSQL
            .Prepared = False
            .Parameters.Append .CreateParameter("param1", adDBDate, adParamInput, 10, DTPicker2.Value)
            .Parameters.Append .CreateParameter("param2", adDBDate, adParamInput, 10, DTPicker3.Value)
           
            Set rst = cmm.Execute
           
            Do Until rst.EOF
                Debug.Print rst.Fields("date")
                rst.MoveNext
            Loop
           
        End With
    End If

End With

err1:
    If Err.Number <> 0 Then
        MsgBox Err.Description
    End If
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cmm = Nothing
    Set cnn = Nothing
    Err.clear
End Sub

---