[pathllk]

I want to filter my records using date range. I used two dtpicker control to filter the records. I write some codes for this, the codes are as given below:-
[code]
Private Sub Label17_Click()
Dim DTT1, DTT2 As Date
Dim qqq As String
DTT1 = DT1.Value
DTT2 = DT2.Value
'DT1 AND DT2 ARE DTPICKER CONTROL
CON.Open ("provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\DB1.mdb")
qqq = "SELECT * FROM PMR WHERE DOS BETWEEN '" & DTT1 & "' AND '" & DTT2 & "' ORDER BY DOS"
If RS4.State And adStateOpen Then RS4.Close
RS4.Open qqq, CON, adOpenDynamic, adLockOptimistic
Do Until RS4.EOF
Set LIST2 = lv1.ListItems.Add(, , RS4!SNAME & "")
LIST2.SubItems(1) = RS4!SID & ""
LIST2.SubItems(2) = RS4!ENGINE_NO & ""
LIST2.SubItems(3) = RS4!DOS & ""
LIST2.SubItems(4) = RS4!STYPE & ""
LIST2.SubItems(5) = RS4!HMR & ""
LIST2.SubItems(6) = RS4!REPORT & ""
LIST2.SubItems(7) = RS4!Technician & ""
LIST2.SubItems(8) = RS4!METERIAL & ""
LIST2.SubItems(9) = RS4!CUST & ""
LIST2.SubItems(10) = RS4!recid & ""
RS4.MoveNext
Loop
Set LIST2 = Nothing
CB1.Text = "By Technician"
CBO3.Text = "By Customer"
Frame1.Height = 580
Label10.Enabled = True
RS4.Close
CON.Close
Label14.Caption = lv1.ListItems.Count
End Sub
[\CODE]

A error is shown for the 10th Line i.e. "Data type mismatch in criteria expression". Please friend help me regarding this.

[tlhIn`toq]

Please edit your post to fix the code tags

If RS4.State And adStateOpen Then RS4.Close

This would be the 10th line.

We don't see where either of these variables are created.
Are they both booleans?

[raziel_]

soooo first of all its DatePicker.Value also make sure your Database date format is the same as the format as your date from DTP controls. you can use SQL Format Method

This post has been edited by raziel_: 20 January 2012 - 09:23 AM

[pathllk]

My Edited Post, Please help

Private Sub Label17_Click()
Dim DTT1, DTT2 As Date
Dim qqq As String
DTT1 = DT1.Value
DTT2 = DT2.Value
'DT1 AND DT2 ARE DTPICKER CONTROL
CON.Open ("provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\DB1.mdb")
qqq = "SELECT * FROM PMR WHERE DOS BETWEEN '" & DTT1 & "' AND '" & DTT2 & "' ORDER BY DOS"
If RS4.State And adStateOpen Then RS4.Close
RS4.Open qqq, CON, adOpenDynamic, adLockOptimistic
Do Until RS4.EOF
Set LIST2 = lv1.ListItems.Add(, , RS4!SNAME & "")
LIST2.SubItems(1) = RS4!SID & ""
LIST2.SubItems(2) = RS4!ENGINE_NO & ""
LIST2.SubItems(3) = RS4!DOS & ""
LIST2.SubItems(4) = RS4!STYPE & ""
LIST2.SubItems(5) = RS4!HMR & ""
LIST2.SubItems(6) = RS4!REPORT & ""
LIST2.SubItems(7) = RS4!Technician & ""
LIST2.SubItems(8) = RS4!METERIAL & ""
LIST2.SubItems(9) = RS4!CUST & ""
LIST2.SubItems(10) = RS4!recid & ""
RS4.MoveNext
Loop
Set LIST2 = Nothing
CB1.Text = "By Technician"
CBO3.Text = "By Customer"
Frame1.Height = 580
Label10.Enabled = True
RS4.Close
CON.Close
Label14.Caption = lv1.ListItems.Count
end sub

[raziel_]

edit your code first.
shouldn't this line If RS4.State And adStateOpen Then RS4.Close be If RS4.State = adStateOpen Then RS4.Close also you may want to check if your datepicker .Value property returns the correct format as its in your database. also its DTT1.Value if we must be correct

[BobRodes]

raziel_, on 20 January 2012 - 08:22 PM, said:

edit your code first.
shouldn't this line If RS4.State And adStateOpen Then RS4.Close be If RS4.State = adStateOpen Then RS4.Close

Actually, anding will work too. The possible values are 0, 2, 4, 8, and 16, meaning there are 5 flags. In this particular situation, the states can't be occurring simultaneously, so you can evaluate state using equals. However, if, as is often the case, you want to evaluate one of several flags, you will need to And out the one you want rather than check equals.

[SagaV]

Some tips:

Dim DTT1, DTT2 As Date

The above dimensions the first variable as variant, you need to explicitly specify the type. Change to

Dim DTT1 as date, DTT2 As Date

qqq = "SELECT * FROM PMR WHERE DOS BETWEEN '" & DTT1 & "' AND '" & DTT2 & "' ORDER BY DOS"

Your statement that opens the database clearly indicates that you are using an Access MDB, so you need to format the dates accordingly. Change the above to

qqq = "SELECT * FROM PMR WHERE DOS BETWEEN #" & format$(DTT1,"mm/dd/yyyy") & "# AND #" & format$(DTT2,"mm/dd/yyyy") & "# ORDER BY DOS"

Some ideas...

No need to carry the value from the date pickers to the date variables and then to the query. Another approach is to declare the two date variables as string, format them as above and then include them in the query. Something like:

dim DTT1 as string, DTT2 as string

DTT1 = format$(DT1.Value,"mm/dd/yyyy")
DTT2 = format$(DT2.Value,"mm/dd/yyyy")

:
:
qqq = "SELECT * FROM PMR WHERE DOS BETWEEN #" & DTT1 & "# AND #" & DTT2 & "# ORDER BY DOS"

If at all possible, avoid using *; instead spell put each field name. Yeah, that's more of a hassle, but it may make the fetch operation more efficient.

Good luck! Saga