how to read data from two date time?

I need to read record dataset from two date time in a ACCESS DATABASE. the program is given below

-----------------------------------------------------------------------------------------------------------------------
kname = Path & "\DATABASE\HYDROMETEO.mdb"
cn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cn.Open kname
Set cmd.ActiveConnection = cn
rs1.CursorLocation = adUseClient

bname = "Rain"
SQL = "select R1, R2 from " & bname & " where DT1 between " & CStr(DateBegin) & " and " & CStr(DateOver) & " order by DT1 asc"
rs1.Open SQL, cn
-----------------------------------------------------------------------------------------------------------------------
however, I cannot fulfill this task. where DateBegin & DateOver is date time format such as 1900/07/01 12:30:00. If I deleted the time in the date time format, it runs normally. So I wonder if anyone can give me some advices on this error. Thank you.

Use PARAMETERS instead of whatever the user inputs. Otherwise, he can add a few crafted things to give unwanted results. Look at the difference below:

Code:

---

Option Explicit

Private Sub Form_Load()
  ParamAdd
End Sub

Sub ParamAdd()
Dim cmdSQLInsert As ADODB.Command
Set cmdSQLInsert = New ADODB.Command

'Create the query
cmdSQLInsert.CommandText = "Insert Into Table1(ID, NAME, AGE) Values(?,?,?)"
cmdSQLInsert.CommandType = adCmdText
cmdSQLInsert.Prepared = True

'Create the parameters
'in this case we will create three parameters
'-----Param 1 (for Field ID)-------------
Dim gParam As ADODB.Parameter
Set gParam = New ADODB.Parameter
With gParam
    .Name = "ID"
    .Direction = adParamInput
    .Type = adChar
    .Size = 10
    .Value = "xxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam

'-----Param 2 (for Field Name)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
    .Name = "NAME"
    .Direction = adParamInput
    .Type = adVarChar
    .Size = 50
    .Value = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam

'-----Param 3 (for Field AGE)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
    .Name = "AGE"
    .Direction = adParamInput
    .Type = adChar
    .Size = 2
    .Value = "xx"
End With
cmdSQLInsert.Parameters.Append gParam

'Set the connection property of the command object
Set cmdSQLInsert.ActiveConnection = mySQLConnection
'pass the values that need to be inserted to specific parameters that we created above
cmdSQLInsert("ID") = txtID.Text
cmdSQLInsert("NAME") = txtID.Text
cmdSQLInsert("AGE") = txtAge.Text

'Execute the command
cmdSQLInsert.Execute
End Sub

---

If the fields are date type in the mdb then you must use # signs around them in MS Access

Code:

---

SQL = "select R1, R2 from " & bname & " where DT1 between #" & CStr(DateBegin) & "# and #" & CStr(DateOver) & "# order by DT1 asc"

---

Quote:

---

Originally Posted by DataMiser

If the fields are date type in the mdb then you must use # signs around them in MS Access

Code:

---

SQL = "select R1, R2 from " & bname & " where DT1 between #" & CStr(DateBegin) & "# and #" & CStr(DateOver) & "# order by DT1 asc"

---

---

Thank you. I got it.