Filter subform based on button click from main form? (1 Viewer)

kawai6rr

Registered User.
Local time
Today, 16:02
Joined
Aug 26, 2009
Messages
21
Here’s my issue. I have a form with a subform. I’m trying to filter my subform with a button click and two text boxes on the main form. The two text boxes are the begin date and then end date which filter the subform based on the date in the subform. When I click the button it only shows the first record.

Private Sub btnFilter_Click()
Dim Beginning As Date
Dim Ending As Date

Beginning = Me.txtBeginDate.Value
Ending = Me.txtEndDate.Value

If Not IsNull(Beginning) And Not IsNull(Ending) Then
Me!outputWindow.Form.RecordSource = "SELECT firstName, LastName, department, fppeID, fppeDate FROM FPPE WHERE fppeDate >= '" & Beginning & "' AND fppeDate <= '" & Ending & "' ORDER BY " & fppeDate & " "
'DoCmd.Requery
Else
MsgBox "you need to enter a beginning and end date!"
End If

End Sub

Does anyone have any ideas on how to solve this?

Thanks!!
 

SOS

Registered Lunatic
Local time
Today, 16:02
Joined
Aug 27, 2008
Messages
3,517
You need to change your WHERE to this:

WHERE fppeDate >= #" & Beginning & "# AND fppeDate <= #" & Ending & "# ORDER BY " & fppeDate

No need to have the & " " at the end.

You can also use BETWEEN

WHERE fppeDate Between #" & Beginning & "# AND #" & Ending & "# ORDER BY " & fppeDate
 

kawai6rr

Registered User.
Local time
Today, 16:02
Joined
Aug 26, 2009
Messages
21
This is doing the same thing as my original string

Me!outputWindow.Form.RecordSource = "SELECT firstName, LastName, department, fppeID, fppeDate FROM FPPE WHERE fppeDate >= #" & Beginning & "# AND fppeDate <= #" & Ending & "# ORDER BY " & fppeDate

I should have mentioned that the subform is only displaying a list of people that the main form does just in a different format so we can get a monthly look at who's done what. Would this be done better as a listbox? And if so how do you reference the listbox? Sorry I still pretty new to programming.
 

Users who are viewing this thread

Top Bottom