Check Box to Turn On/Off a Filter (1 Viewer)

[themurph2000]

I apologize for the caffeine not kicking in...

I have a form based on a query that filters a series of vacation records. There's a separate field that tells the database what year to charge the vacation to (just in case people use 2011 vacation hours in 2012 or vice versa). What I want to do is use a check box to let people filter only the records charged to the current year vs all records from past years. As you might suspect, it isn't working. The check box, when on, seems to work. But when it's unchecked, no records show at all. The criteria is...

IIf([Forms]![frmEnterEmployeeVacationTime]![filteron]=True,DatePart("yyyy",Date()),([tblVACATION LOG].[Charged to What Year]) Like "*")

Any suggestions? Thanks

 

[themurph2000]

I apologize for the caffeine not kicking in...

I have a form based on a query that filters a series of vacation records. There's a separate field that tells the database what year to charge the vacation to (just in case people use 2011 vacation hours in 2012 or vice versa). What I want to do is use a check box to let people filter only the records charged to the current year vs all records from past years. As you might suspect, it isn't working. The check box, when on, seems to work. But when it's unchecked, no records show at all. The criteria is...

IIf([Forms]![frmEnterEmployeeVacationTime]![filteron]=True,DatePart("yyyy",Date()),([tblVACATION LOG].[Charged to What Year]) Like "*")

Any suggestions? Thanks

In the words of Emily Litella...NEVER MIND!

Thanks to this thread...

http://www.access-programmers.co.uk/forums/showthread.php?t=170924

I was able to just turn on and off the filter for the records so that the check box would show only the current year if turned on.

    If Me.filtercheck = True Then
        Me.Filter = "[Charged to What Year] = Year(Date())"
        Me.filteron = True
    Else
        Me.filteron = False
    End If

 

[Privateer]

Hey Murph, I would make one other recommendation. When dealing with dates, use dates. Your check box only allows for two years, this year and all years. What if I want 2009? I would get rid of the check box and use a combo with all the possible years. You can even add a line (All Years) to the top of the combo rowsource. Also with dates there is usually a start date and an end date and Access has a between option which is great. So if you put two date fields on the form you can get results between 2009 and 2012 which gives you every possible option. Just a suggestion.
Privateer

 

[themurph2000]

Hey Murph, I would make one other recommendation. When dealing with dates, use dates. Your check box only allows for two years, this year and all years. What if I want 2009? I would get rid of the check box and use a combo with all the possible years. You can even add a line (All Years) to the top of the combo rowsource. Also with dates there is usually a start date and an end date and Access has a between option which is great. So if you put two date fields on the form you can get results between 2009 and 2012 which gives you every possible option. Just a suggestion.
Privateer

Which I do in other sections, but for the form I described here, they only needed two modes: filtering the records for the current year, then all records. They wouldn't use this particular form to look up a single year in the past. That's why I could get away with the check box. I use combo boxes in other locations to filter the queries behind the reports they wish to print.