Filter on Load

[cktcPeterson]

I have a form with a query that I want to filter on load

between 10/1/22 and 9/30/23.

I then want a check box that I can click to view all records.
When checked- show all records
Unchecked- between 10/1/22 and 9/30/23

I saw this on a demo access but can not figure it out. Need some guidance.

Thanks

 

[XPS35]

So the default value of checkbox is "unchecked"?

If so, make a after update event on the checkbox to set the forms FilterOn property to TRUE or FALSE depending on the checkbox value. In form design set it TRUE.

 

[Gasman]

And what will be the filter range next year?

 

[cktcPeterson]

What I would really like is 5 toggle buttons.
Year 1
Year 2
Year 3
Year 4
Year 5.

I can select 1 or all.

The dates would be based on a federal fiscal year.

Default would be the current fiscal year. 10/1/22-9/30/23

I know the table has a between, but I need guidance on setting up the onload filter. And so on.

 

[Gasman]

I would use a combo in that case, as otherwise when Year 6 comes around, you need to amend the form.
Then you can have an All option as well with no filter.?

 

[cktcPeterson]

So the default value of checkbox is "unchecked"?

If so, make a after update event on the checkbox to set the forms FilterOn property to TRUE or FALSE depending on the checkbox value. In form design set it TRUE.

How to I do the filter on load to be a date range?

 

[cktcPeterson]

I have this
Filter: ([qryRequested Course_View].[Completion time] =Between #10/1/2022# And #9/30/2023#)
Filter On Load: Yes

I am getting a syntax error.

 

[XPS35]

Try quotes around the filter: "[Completion time] =Between #10/1/2022# And #9/30/2023#"

 

[moke123]

You can also use a fiscal year function

Function GetFiscalYear(Dt As Variant, Optional StartMonth As Integer = 9) As Variant

    If Not IsDate(Dt) Then Exit Function
    
    If Month(Dt) >= StartMonth Then
    
        GetFiscalYear = (Year(Dt) + 1)
        
    Else
    
        GetFiscalYear = Year(Dt)
        
    End If
    
End Function

Here's an example using a dynamic option group. I set it up to always be 2 years back and 2 years forward and an "all" selection. The year labels will change with the year.

 

[cktcPeterson]

Try quotes around the filter: "[Completion time] =Between #10/1/2022# And #9/30/2023#"

Nope, still getting all the records.

 

[moke123]

Get rid of the equal sign.

"[Completion Time] Between #10/1/2022# And #9/30/2023#"

 

[cktcPeterson]

Get rid of the equal sign.

"[Completion Time] Between #10/1/2022# And #9/30/2023#"

After trial and error this finally worked.
[qryRequested Course_View].[Completion Time] Between #10/1/2022# And #9/30/2023#

 

[cktcPeterson]

So the default value of checkbox is "unchecked"?

If so, make a after update event on the checkbox to set the forms FilterOn property to TRUE or FALSE depending on the checkbox value. In form design set it TRUE.

Can you guide me on how to do this part?

I have the check box and the default value is 0 (unchecked)
As for the after update, how do I do that? Macro or Code?

box name: ckbxSeeAll

When checked-I want to clear the filter on load.

Unchecked

 

[XPS35]

You best use code.
In design mode go to the properties of the checkbox and select the events tab. Create an after update event to set the filter property of the form according to the value of the checkbox. Something like

If me.YourCheckbox = True Then
   me.Filter = "YourDate BETWEEN #1O/01/2022# AND #9/30/2023#"
Else
   me.Filter = ""
EndIf

 

[cktcPeterson]

You best use code.
In design mode go to the properties of the checkbox and select the events tab. Create an after update event to set the filter property of the form according to the value of the checkbox. Something like

If me.YourCheckbox = True Then
   me.Filter = "YourDate BETWEEN #1O/01/2022# AND #9/30/2023#"
Else
   me.Filter = ""
EndIf

Thank you I am getting closer. I now have this. How do I filter back to the filter on load?

My filter on load is 10/1/22-9/30/23
when I use the code below it works perfectly and unfilters.
When check box is clicked again, I want it to go back. to the filter on load.

If me.YourCheckbox = False Then
me.Filter = "YourDate BETWEEN #1O/01/2022# AND #9/30/2023#"
Else
me.Filter = ""
EndIf

 

[moke123]

Cant you put that in the else portion?

 

[XPS35]

when I use the code below it works perfectly and unfilters.
When check box is clicked again, I want it to go back. to the filter on load.

The code should do that. Of course you need to replace "YourDate" with the name of your date field.

Unfortunately I can't test it today.

 

[moke123]

What are you going to do when you hit the next fiscal year? If you are hard coding the filter you'll have to change it each year.

It would seem to me that all you want to do is toggle the filter on or off.

In the attached I have a field in the forms record source which is the fiscal year for the date field.
A combo box has a row source of all the distinct FY's.
The check box toggles the filter on or off.

 

[cktcPeterson]

The code should do that. Of course you need to replace "YourDate" with the name of your date field.

Unfortunately I can't test it today.

Ah, that makes sense. It was working with that in there. I'll try again.

 

[moke123]

if there are only 2 choices you simply need

me.filter = "[Completion Time] Between #10/1/2022# And #9/30/2023#"

me.filteron = not me.ckbxSeeAll