check Box

[Guest]

I have this form, on the form there is 5 check boxes for years -2006, 2005,
2004, 2003 and All Years. i want that the user can filter on what year they
want. The user should be able to check more then one check box. Like if the
user wants to filter on only 2006 they can and if they want to filter on 2006
and 2004, or 2005, 2004 & 2003. It should be open to what they want to
filter on. I dont have them in a Option group. But can i Still use the same
code like the one below or their is another way of doing this. Please Help!

Select Case Me.frayear.Value
Case 1
stryear = "='2006'"
Case 2
stryear = "='2005'"
Case 3
stryear = "='2004'"
case 4
stryear = "='2003'"
Case 5
stryear = "like '*'"
End Select

 

[Guest]

If they are not in a option group, you have to evaluate the value of each one
seperately. I would suggest a module-level variable to store a filter string.
In the AfterUpdate of each check box, call some code like:

strFilter=""
If Not chkAllYears Then
If chk2003 Then
strFilter="2003 or "
End If
If chk2004 Then
strFilter = strFilter & "2004 or "
End If
If chk2005 Then
strFilter = strFilter & "2005"
End If
If Right$(strFilter,4) = " or " Then
strFilter = Left$(strFilter, Len(strFilter) - 4)
End If
End If
Me.Filter = "Year = " & strFilter

HTH,
Barry

 

[John Nurick]

Is the field on which you want to filter a date/time field, a number
field containing the year (e.g. 2003), or a text field containing the
year (e.g. "2003")?


If you have a field that contains just the year, think in terms of
building something like this, where the field is named TheYear:

WHERE [TheYear] IN (2003, 2004, 2006)

or for a text field

WHERE [TheYear] IN ('2003', '2004', '2006')

If it's a date/time field, you could do something similar, e.g.

WHERE Year([TheDate]) IN (2003, 2005)

but if there are thousands of records and the field is indexed it would
be faster to use the BETWEEN ... AND structure:

WHERE ([TheDate] BETWEEN #01/01/2003# AND #12/31/2003)
OR ([TheDate] BETWEEN #01/01/2005# AND #12/31/2005#)



Because you need each checkbox to work independently (so the user can
select more than one) you can't use a frame: you need to refer to each
individually. The value of a checkbox is -1 if it's checked.

But before you go further, think ahead. Do you want to spend New Year's
Day redesigning this form?

 

[Guest]

the field is just year. tits just a number field containing the year.

Is the field on which you want to filter a date/time field, a number
field containing the year (e.g. 2003), or a text field containing the
year (e.g. "2003")?


If you have a field that contains just the year, think in terms of
building something like this, where the field is named TheYear:

WHERE [TheYear] IN (2003, 2004, 2006)

or for a text field

WHERE [TheYear] IN ('2003', '2004', '2006')

If it's a date/time field, you could do something similar, e.g.

WHERE Year([TheDate]) IN (2003, 2005)

but if there are thousands of records and the field is indexed it would
be faster to use the BETWEEN ... AND structure:

WHERE ([TheDate] BETWEEN #01/01/2003# AND #12/31/2003)
OR ([TheDate] BETWEEN #01/01/2005# AND #12/31/2005#)



Because you need each checkbox to work independently (so the user can
select more than one) you can't use a frame: you need to refer to each
individually. The value of a checkbox is -1 if it's checked.

But before you go further, think ahead. Do you want to spend New Year's
Day redesigning this form?

I have this form, on the form there is 5 check boxes for years -2006, 2005,
2004, 2003 and All Years. i want that the user can filter on what year they
want. The user should be able to check more then one check box. Like if the
user wants to filter on only 2006 they can and if they want to filter on 2006
and 2004, or 2005, 2004 & 2003. It should be open to what they want to
filter on. I dont have them in a Option group. But can i Still use the same
code like the one below or their is another way of doing this. Please Help!

Select Case Me.frayear.Value
Case 1
stryear = "='2006'"
Case 2
stryear = "='2005'"
Case 3
stryear = "='2004'"
case 4
stryear = "='2003'"
Case 5
stryear = "like '*'"
End Select

 

[Guest]

Hey Barry

the code you gave do i put that behide each each check box right. in the
afterupdate
so for check box 2004 i would put

If chk2004 Then
strFilter = strFilter & "2004 or "
End If

and so on.
what about this one

If Right$(strFilter,4) = " or " Then
strFilter = Left$(strFilter, Len(strFilter) - 4)
End If

or the whole code goes behide all check box.

sorry bit confused here.