Newsgroup: it.comp.lang.visual-basic
Titolo: Help me about Filter

hi all....
i have good experience with vb
but i have some serious problems
i have created a mysql with table dss
but i`m confused about this
i think many people will have this problem in future
this is the table of dss
==================
date (data type = date)
drivers(data type = text)
group (data type = text)
speed(data type = number)
score (data type =number)
==================
-----------------------------------------------------------------
Date | Drivers | Group | Speed | Score |
-----------------------------------------------------------------
01/01/2006 2111 north 64 98
02/01/2006 2121 north 53 95
03/01/2006 2145 north 43 96
04/01/2006 2187 south 77 94
05/01/2006 2133 north 35 92
06/01/2006 2165 south 38 100
07/01/2006 2181 north 38 80
08/01/2006 2135 north 42 60
09/01/2006 2111 north 43 44
10/01/2006 2124 south 41 85
11/01/2006 2113 north 45 82
12/01/2006 2109 north 75 96
13/01/2006 2106 north 75 99
14/01/2006 2111 north 75 87
15/01/2006 2123 south 90 56
--------------------------------------------------------
then i want select the date from 01/01/2006 until 10/01/2006 with
the driver is 2111 with group north and with speed from 24 until 90
with score from 50 until 100 and show it into datagrid
but i can selected any in checkbox and unselected to show what field i
just wanna filtered
i made date with dtpicker and drivers,group speed and score with
textbox
then i made selected field that i want to filtered with checkbox
this is the details
==================================
check1.value for date
check2.value for drivers
check3.value for group
check4.value for speed
check5.value for score
==================================
date1 = dtpicker1.value
date2 = dtpicker2.value
drivers = txtdrivers.text
group = cbogroups.text (combo box)
speed = txtspeed1.text and txtspeed2.text
score = txtscore1.text and txtscore2.text
==================================
this is a link the picture of the forms
http://www.geocities.com/joanleonz/forms.GIF
i has been wite some code...(still error )
but i want with checkbox like the forms on
http://www.geocities.com/joanleonz/forms.GIF
so i can pick up what field that i want to be filtered
this is the code
---------------------------------------------------------------------------­------
Private Sub Form_Load()
con.ConnectionString = _
"PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.4.0;Data
Source=" & App.Path & "\database.mdb;"
con.Open
Set rssppd = New Recordset
rssppd.ActiveConnection = con
rssppd.CursorType = adOpenStatic
rssppd.CursorLocation = adUseClient
rssppd.LockType = adLockOptimistic
rssppd.Open "select * from dss"
Set DataGrid1.DataSource = rssppd
End Sub
---------------------------------------------------------------------------­---
Private Sub cmdfilter_Click()
Set rssppd = New Recordset
rssppd.ActiveConnection = con
rssppd.CursorType = adOpenStatic
rssppd.CursorLocation = adUseClient
rssppd.LockType = adLockOptimistic
strsql = "select * from dss where date between date >='" &
(dtpicker1.Text) & "'" & _
"and date <='" & (dtpicker2.Text) & "'" & " and drivers='" &
txtdrivers.Text & "'" & _
"and groups='" & cbogroups.Text & "'" & _
"and speed>='" & txtspeed1.Text & "'" & _
"and speed>='" & txtspeed2.Text & "'" & _
"and score>='" & txtscore1.Text & "'" & _
"and score>='" & txtscore2.Text & "'"
rssppd.Open strsql, con, adOpenStatic, adLockBatchOptimistic
end sub
please help me what kind of code what must i have
please help me? URGENT! 
thanks

 ==================================
Dim SQLStr as string, ss as string
Dim UseAnd as boolean
UseAnd=False
ss=""
SQLStr ="SELECT * FROM TABLE"
if txtdrivers.text<>"" and check2.value<>0 then
  if UseAnd then ss= ss & " AND "  
  UseAnd=True
  ss= ss & " DRIVERS = '" & txtdrivers.text & "'"
end if
'repeat it for every field/check (for speed and score use BETWEEN
txtspeed1.text AND txtspeed2.text)
if ss<>"" then
  sqlstr=sqlstr & " WHERE " & ss
endif
Now if  you have an Ado Recordset:
AdoRecordset.Source= SQLStr
AdoRecordset.Open
if not AdoRecordset.EOF then
  'your code
endif
AdoRecordset.Close
-- 
DBMS Firebird
VB6 + Ado

yes sure
look my code
=======================================
bAtLeastOne = False
mstrSQL = "SELECT * from dss WHERE "
If Check1.Value = vbChecked Then
mstrSQL = mstrSQL & "(Date>= #" & txtdate1.Text & "#) AND (Date<= #" &
txtdate2.Text & "#)"
bAtLeastOne = True
End If
If Check2.Value = vbChecked Then
If bAtLeastOne Then
mstrSQL = mstrSQL & " AND "
End If
mstrSQL = mstrSQL & " driver='" & txtdriver.Text & "'"
bAtLeastOne = True
End If
If Check3.Value = vbChecked Then
If bAtLeastOne Then
mstrSQL = mstrSQL & " AND "
End If
mstrSQL = mstrSQL & " groups='" & txtdriver.Text & "'"
bAtLeastOne = True
End If
If Check4.Value = vbChecked Then
If bAtLeastOne Then
mstrSQL = mstrSQL & " AND "
End If
mstrSQL = mstrSQL & "(speed>= #" & txtspeed1.Text & "#) AND (Date<= #"
& txtspeed2.Text & "#)"
bAtLeastOne = True
End If
If Check5.Value = vbChecked Then
If bAtLeastOne Then
mstrSQL = mstrSQL & " AND "
End If
mstrSQL = mstrSQL & "(score>= #" & txtscore1.Text & "#) AND (score<= #"
& txtscore2.Text & "#)"
bAtLeastOne = True
End If
rsdss.Open mstrSQL, con, adOpenStatic, adLockOptimistic
=======================================
forms : 
http://www.geocities.com/joanleonz/forms.GIF
but still error
error messages is no value given for one or more required parameters
i dont know why
please tell me what code that must i have


 please tell me what code that must i have
Try to filter to 1 field at times to find problem.
If the problem persist with all type of filter try to copy the final
mstrsql in a console of database and run it from here to find error.
