Jarichardusa
New member
- Local time
- Today, 15:39
- Joined
- May 23, 2020
- Messages
- 19
I have a form, with a data sheet subform. This subform has rows with WageDate and TipDate. I have the follwing bit of code to filter the the records to display the records with the WageDate that falls in the range:
My question is how do I change this up so that it also filters the TipDate at the same time, so that the resulting records would be those with both WageDate AND TipDate that fall in the selected range? the records might not be equal. That is, one record may have a WageDate that falls out of the range, but a TipDate that does.
And one I get that figured out, is there a way to selective SUM a column based on that date? For instance. Let's say I filter a date range and four records are returned two have WageDates that fall in the criteria, and two have TipDates thats fall in the criteria. WageDate and TipDate are both in the same datasheet. So If were to just do a SUM of Wage and TipAmount columns I'd get a sum which would include numbers in those columns that don't really belong. The TipAmount column might have an ammount come up because the WageDate fell in the range but not the TipDate. So I need to be able to SUM just the rows in each of those columns that ONLY fall within the selected daterange. I hope this wasn't too confusing. Maybe there's a better way to do this completely than the way I have it, I don't know. oh, for information, the subform is based from a query, and both wages and tips are in there own separate tables. tblWages, tblTips
Code:
Private Sub btnDateRange_Click()
Dim Filter As String
Filter = "[WageDate] Between #" & Format(Me!TxtStartDate.Value, "yyyy\/mm\/dd") & "# And #" & Format(Me!txtEndDate.Value, "yyyy\/mm\/dd") & "#"
Me!sbfrm_qryDeliveries.Form.Filter = Filter
Me!sbfrm_qryDeliveries.Form.FilterOn = True
End Sub
My question is how do I change this up so that it also filters the TipDate at the same time, so that the resulting records would be those with both WageDate AND TipDate that fall in the selected range? the records might not be equal. That is, one record may have a WageDate that falls out of the range, but a TipDate that does.
And one I get that figured out, is there a way to selective SUM a column based on that date? For instance. Let's say I filter a date range and four records are returned two have WageDates that fall in the criteria, and two have TipDates thats fall in the criteria. WageDate and TipDate are both in the same datasheet. So If were to just do a SUM of Wage and TipAmount columns I'd get a sum which would include numbers in those columns that don't really belong. The TipAmount column might have an ammount come up because the WageDate fell in the range but not the TipDate. So I need to be able to SUM just the rows in each of those columns that ONLY fall within the selected daterange. I hope this wasn't too confusing. Maybe there's a better way to do this completely than the way I have it, I don't know. oh, for information, the subform is based from a query, and both wages and tips are in there own separate tables. tblWages, tblTips