Applying filters on subform

[as0125]

Hello all,

I have a main form that contains two textboxes (FromDate and ToDate) and a button (ApplyDates). When clicking on the button, I'd like to use the dates inputted into the textboxes to filter out data in a subform (SubByDate). The data displayed should be between the two dates inputted. Here is my code:

Private Sub ApplyDates_Click()

Dim FromFilter As String
Dim ToFilter As String

FromDate = Me.FromDate
ToDate = Me.ToDate

FromFilter = "#" & FromDate & "#"
ToFilter = "#" & ToDate & "#"

If Len(strFilter) Then
Forms!SubByDate.Filter = "[ReleaseDate] >=" & FromFilter & "AND [ReleaseDate] <=" & ToFilter
Forms!SubByDate.FilterOn = True
End If

End Sub

My problem is that nothing happens when clicking on the button. Can anyone tell me what I'm missing/doing wrong?

Thanks!

 

[PHV]

Put an Option Explicit line in the 1st line of the Declarations section to discover that qtrFilter isn't defined.
Then try this:
Me!SubByDate.Form.Filter = "[ReleaseDate] >=" & FromFilter & " AND [ReleaseDate] <=" & ToFilter
Me!SubByDate.Form.FilterOn = True


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[as0125]

Hi PH,

I followed your suggestions and now receive this error message when clicking on the "ApplyDates" button:

This is the error received when using
Me!SubByDate.Form.Filter = "[ReleaseDate] >=" & FromFilter & " AND [ReleaseDate] <=" & ToFilter

Run-time error '2465':
Microsoft Office Access can't find the field 'SubByDate' referred to in your expression.


However, if I substitute "Me!" with "Forms!" instead, the runtime error states that it can't find the form 'SubByDate'.

 

[PHV]

Replace this:
Me!SubByDate.Form.Filter
By something like this:
Me![name of the control hosting the SubByDate form].Form.Filter

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[as0125]

Sorry, PH. I'm new to both Access and VBA and don't follow what you're saying.

 

[PHV]

You can retrieve the exact syntax to use with the expression builder trying to get the value of a control in your subform.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Flo79]

There is another way that in my opinion is much simpler than using vb to accomplish this task:

If your subform isn't already based on a query then create one and use it as the source of your subfrom.
Incorporate the date field you want to filter by into the query and include your desired criteria in the criteria section below

>"#11/23/2003#" AND < "#12/23/2003#"

Run the query to test that it works first.

In order to use the fields on your main form change the above criteria as follows:

>forms![Name of Your Form]![Name of the MIN Date Field]or is null or forms![Name of Your Form]![Name of the MIN Date Field] is null AND < forms![Name of Your Form]![Name of the MAX Date Field] or forms![Name of Your Form]![Name of the MAX Date Field] is null

Now,

use the requeryall action to refresh the data in your subform to reflect the desired results.
I usually use a macro that is triggered by a botton on my main form to do this.

Hope this Helps.... Cheers!

 

[as0125]

Great! thanks for your help. It's working now!