How to find the date range in MS Access and display in MS Flex Grid?

[tauhu82]

How do i search the date range which can be specified by the user and all the records from these date range will be display in MSFlexGrid. Can someone help me with this??

Thanks.

[ganeshmoorthy]

if it is Access use this query...

Sql = "Select * From <TableName> Where MyDate Between DateValue ('" & txtFromDate.Text & "') And DateValue ('" & txtToDate.Text & "')"
Rs.Open Sql, Cn, adOpenDynamic, adLockOptimistic
i = 1
Do While Not Rs.EOF 
    FlexGrid.TextMatrix(i, 1) = Rs!FieldName1
    FlexGrid.TextMatrix(i, 2) = Rs!FieldName2
    FlexGrid.TextMatrix(i, 3) = Rs!FieldName3
    ' and so on
    i = i + 1
    Rs.MoveNext    
Loop
Rs.Close

[tauhu82]

I'm having error on this line

Rs.Open Sql, Cn, adOpenDynamic, adLockOptimistic

Run time error '424'
Object Required

Can somebody help me.

[tauhu82]

Private Sub Form_Load()
Dim Sql
Dim Rs As Recordset
Dim Cn

Set Cn = New ADODB.Connection
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "control.mdb;" & "Persist Security Info=False"

Sql = "Select * From Kdata Where Tarikh Between DateValue ('" & txtFromDate.Text & "') And DateValue ('" & txtToDate.Text & "')"

Rs.Open Sql, Cn, adOpenDynamic, adLockOptimistic
i = 1
Do While Not Rs.EOF
MSFlexGrid1.TextMatrix(i, 1) = Rs!No_Rujukan
MSFlexGrid1.TextMatrix(i, 2) = Rs!Tarikh
MSFlexGrid1.TextMatrix(i, 3) = Rs!Masa_Mula
' and so on
i = i + 1
Rs.MoveNext
Loop
Rs.Close
End Sub


What is wrong with this line " Rs.Open Sql, Cn, adOpenDynamic, adLockOptimistic "?? It is saying Run time error '91' Object variable or with bolck variable not set.

Please help.

[brucevde]

You need to create the recordset object with

Set rs = New ADODB.Recordset

Just like you did with the Connection object.

[tauhu82]

Private Sub Form_Load()
Dim Sql
Dim Rs As Recordset
Dim Cn


Set Cn = New ADODB.Connection
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "control.mdb;" & "Persist Security Info=False"

Set Rs = New ADODB.Recordset
Sql = "Select * From Kdata Where Tarikh Between DateValue ('" & txtFromDate.Text & "') And DateValue ('" & txtToDate.Text & "')"

Rs.Open Sql, Cn, adOpenDynamic, adLockOptimistic
i = 1
Do While Not Rs.EOF
MSFlexGrid1.TextMatrix(i, 1) = Rs!No_Rujukan
MSFlexGrid1.TextMatrix(i, 2) = Rs!Tarikh
MSFlexGrid1.TextMatrix(i, 3) = Rs!Masa_Mula
' and so on
i = i + 1
Rs.MoveNext
Loop
Rs.Close
End Sub

Now the error is
Run time error '-2147217913 (80040e07)'
Data type mismatch in criteria expression

Still the error is in this line Rs.Open Sql, Cn, adOpenDynamic, adLockOptimistic
Please give me some suggestion.

[GaryMazzone]

Since you are using MS Access I would change the DateValue part like this:

Sql = "Select * From Kdata Where Tarikh Between #'" & Formamt(CDate(txtFromDate.Text)) & "# And # " & Formamt(CDate(txtToDate.Text)) & "#"

Also ensure that txtFromDate and txtToDate are valid dates before attempting to run the SQL