Help me about Filter

[dokter cinta]

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

 

[Duane Hookom]

You have posted basically the same thing in at least two News Groups. One
group should be all that is required.

Did you have a specific question you would like answered? Are you attempting
to filter a report or form based on values in controls on your form?

BTW: Date is a function name in Access and should not really be used as a
field name. I would also avoid Group since SQL uses Group By.

--
Duane Hookom
MS Access MVP

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

 

[dokter cinta]

http://www.geocities.com/joanleonz/forms.GIF
i want to be like that froms
i can selected / unselected what field that i want to filtered by
clicking checkbox and then click submit(filter) to run that processed
and show it into datagrid
please help me what code that must i have....

===========================================

 

[Duane Hookom]

I don't know how/why use a datagrid rathert than an Access Datasheet
subform. However:
Base your datagrid on a saved query. Use DAO code behind your form (called
from the Filter command button).

Dim strSQL as String
Dim strWhere as String
strSQL = "SELECT * FROM tblSomeTable "
strWhere = " WHERE 1=1 "
IF Not IsNull(Me.Date1) Then
strWhere = strWhere & " AND [Date] >=#" & _
Me.Date1 & "# "
End If
IF Not IsNull(Me.Date2) Then
strWhere = strWhere & " AND [Date] <=#" & _
Me.Date2 & "# "
End If
' add code for more controls
Currentdb.QueryDefs("qselYourQuery").SQL = strSQL & strWhere
Me.datagrid.Requery ' or something similar

--
Duane Hookom
MS Access MVP


http://www.geocities.com/joanleonz/forms.GIF
i want to be like that froms
i can selected / unselected what field that i want to filtered by
clicking checkbox and then click submit(filter) to run that processed
and show it into datagrid
please help me what code that must i have....

===========================================