Help With a date Filter with in a Subform

[tssparky]

Hello guys,

I have A filter that I use to filter a date range of orders, works ok if everything is all on one form. Much the same as the last filter problem I had.

So I have a form with Tab Control, a datasheet of all orders in a subform on "Tab1". On "Tab1" (Main)I have the text boxes and button.

Here is the code I used for (everything on one form)
I have tried to make the field names fairly obvious.

---------------------------------------------
Option Compare Database
---------------------------------------------
Private Sub OrSearch_btn_Click()
Call Search
End Sub
---------------------------------------------
Sub Search()
Dim strCriteria, task As String

Me.Refresh


If IsNull(Me.OrderSearchFrom) Then
MsgBox "Please Enter The Date Range", vbInformation, "Date Range Required"
Me.OrderSearchFrom.SetFocus
Else
strCriteria = "([Order_Date] >= #" & Me.OrderSearchFrom & "# And
[Order_Date] <= #" & Date & "#)"
task = "Select * From Orders Where (" & strCriteria & ") Order by
[Order_Date]"
DoCmd.ApplyFilter task
End If
End Sub

--------------------------------------------------


I have tried to specify the Date column refering to the subform name and with

Forms.[Mainorm]![Subform] .Form![Order_Date] and many other combinations that also didn't work for me. either can't find [Order_Date] Or asking for an input for [Order_date] Or Runtime error, depending on the wrong combination I chose :/

-----------------------------------
Sub Search()
Dim strCriteria, task As String

Me.Refresh


If IsNull(Me.OrderSearchFrom) Then
MsgBox "Please Enter The Date Range", vbInformation, "Date Range Required"
Me.OrderSearchFrom.SetFocus
Else
strCriteria = "([OrderLookup_Sub]![Order_Date] >= #" &
Me.OrderSearchFrom & "# And [OrderLookup_Sub]![Order_Date] <= #" & Date & "#)"
task = "Select * From Orders Where (" & strCriteria & ") Order by
[OrderLookup_Sub]![Order_Date]"
DoCmd.ApplyFilter task

-----------------------------------------------


Any help would be of course very much appreciated..

 

[moke123]

to filter your subform you would use something like

Dim strFilter as string

strFilter = "([Order_Date] >= #" & Me.OrderSearchFrom & "# And [Order_Date] <= #" & Date & "#)"

me.YourSubformName.Filter = strFilter
me.YourSubformName.FilterOn = true

 

[tssparky]

Thanks for that Moke123,


Im getting an error,



Private Sub Date_btn_Click()


Dim strFilter As String

strFilter = "([Order_Date] >= #" & Me.DateFrom & "# And [Order_Date] <= #" & Me.DateTo & "#)"

Me.OpenOrderForm.Filter = strFilter ( Method Or data member not found)
Me.OpenOrderForm.FilterOn = True ( Method Or data member not found)



End Sub




Any other ideas?


Any chance someone can explain when the .AppyFilter is Used as apposed to the .Filter and whats the difference?


Thanks again

 

[isladogs]

PMFBI but moke appears to be offline.

The following assumes your subform control is called OpenOrderForm. If not, replace with the correct name
It MUST be followed by .Form

I've also used Between ...And for the dates as its simpler
Try this:

Private Sub Date_btn_Click()

Dim strFilter As String

strFilter = "[Order_Date] Between #" & Me.DateFrom & "# And #" & Me.DateTo & "#"

Me.OpenOrderForm[B][COLOR="Red"].Form[/COLOR][/B].Filter = strFilter
Me.OpenOrderForm[B][COLOR="red"].Form[/COLOR][/B].FilterOn = True 

End Sub

 

[tssparky]

Thank you, works great.......

 

[isladogs]

You're welcome