Need Help with AND Statement in Date Range Filter and SUM specific records (1 Viewer)

Jarichardusa

New member
Local time
Today, 09:59
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:


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
 

Ranman256

Well-known member
Local time
Today, 09:59
Joined
Apr 9, 2015
Messages
4,339
the valid format is:
Format(Me!TxtStartDate, "yyyy/mm/dd")

but it may not filter on it so try:
Format(Me!TxtStartDate, "mm/dd/yyyy")
which works but you dont have to display this format. Just use it to filter.
 

Jarichardusa

New member
Local time
Today, 09:59
Joined
May 23, 2020
Messages
19
the valid format is:
Format(Me!TxtStartDate, "yyyy/mm/dd")

but it may not filter on it so try:
Format(Me!TxtStartDate, "mm/dd/yyyy")
which works but you dont have to display this format. Just use it to filter.
As written, My form filters perfectly, but only for the one criteria, that is, it is set to filter only WageDate that come in range. What I'm trying to find out is what I need to add to this code to ALSO have it filter TipDate at the same time. But, I'm not sure this is possible, since doing so would also return WageDates that do not fall in the range so maybe the whole thing will get confused.
 

plog

Banishment Pending
Local time
Today, 08:59
Joined
May 11, 2011
Messages
11,611
I'm confused by the ambiguity. Cut through it with sample data. I'll help:

TxtSTartDate=5/1/2020
TxtEndDate=5/15/2020

ID, TipDate, WageDate
1, 5/1/2020, 5/1/2020
2, 4/30/2020, 5/1/2020
3, 5/1/2020, 4/30/2020
4, 5/1/2020, Null
5, Null, 5/1/2020

Which of those 5 records should be included on the form?
 

Jarichardusa

New member
Local time
Today, 09:59
Joined
May 23, 2020
Messages
19
There would be no nulls as all records have a default date set, but of those five all should show.
 

plog

Banishment Pending
Local time
Today, 08:59
Joined
May 11, 2011
Messages
11,611
Code:
...
Filter = "([WageDate] Between #" & CDate(Me!TxtStartDate.Value) & "# And #" & CDate(Me!txtEndDate.Value) & "#)"
Filter = Filter & " OR ([TipDate] Between #" & CDate(Me!TxtStartDate.Value) & "# And #" & CDate(Me!txtEndDate.Value) & "#)"
...

You only need to Format data you plan on displaying. Cdate() it to convert it to a date.
 

Jarichardusa

New member
Local time
Today, 09:59
Joined
May 23, 2020
Messages
19
Code:
...
Filter = "([WageDate] Between #" & CDate(Me!TxtStartDate.Value) & "# And #" & CDate(Me!txtEndDate.Value) & "#)"
Filter = Filter & " OR ([TipDate] Between #" & CDate(Me!TxtStartDate.Value) & "# And #" & CDate(Me!txtEndDate.Value) & "#)"
...

You only need to Format data you plan on displaying. Cdate() it to convert it to a date.
Thank you, that worked perfectly as far as the filtering goes. Now I will attempt to figure out how to work out the second half of this.
 

June7

AWF VIP
Local time
Today, 05:59
Joined
Mar 9, 2014
Messages
5,423
Cross post https://stackoverflow.com/questions/62138643/and-statement-in-date-range-filter

If you don't want record 2 in Tip sum and record 3 not in Wage sum, then need conditional expression that includes same date range as criteria in textbox in form or report footer: Sum(IIf(criteria here, [column], Null))

Otherwise, build two aggregate queries with filter then join them in another query.

What is the link between tblTips and tblWages?

Why different dates for Tip and Wage in same record?
 
Last edited:

Jarichardusa

New member
Local time
Today, 09:59
Joined
May 23, 2020
Messages
19
The link between tblTips and tblWages is tblDeliveries The reason for different dates in the same record is each job has a wage paid by the company for ding the job and a tip by by the customer at some point when the job is completed. The two are paid out depending on the day they were received, so when tying to figure out how much money should be sent to the users bank account on a particular date, it has to use both different dates in the calculations.
Cross post https://stackoverflow.com/questions/62138643/and-statement-in-date-range-filter

If you don't want record 2 in Tip sum and record 3 not in Wage sum, then need conditional expression that includes same date range as criteria in textbox in form or report footer: Sum(IIf(criteria here, [column], Null))

Otherwise, build two aggregate queries with filter then join them in another query.

What is the link between tblTips and tblWages?

Why different dates for Tip and Wage in same record?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:59
Joined
Feb 19, 2002
Messages
42,970
@Ranman256
Format(Me!TxtStartDate, "mm/dd/yyyy")
Just FYI, when you use the Format() function to format a date, you convert it to a string. Formatting a date as a string mm/dd/yyyy will PREVENT it from sorting and comparing correctly. For example

01/01/2020 will sort Before 12/31/2019 and will compare as less. Why? Because string operations work character by character, left to right and 01 is less than 12.

NEVER use the Format() function to format a date you are going to use in a sort or a compare. OR, if you think you must format it, then format as yyyy/mm/dd which will sort and compare as you expect it to.

SQL Server assumes string dates are mm/dd/yyyy which causes a huge amount of confusion. If you write a query:
Select ... From .. Where StartDT < EndDT
You would never have any need to format either date. Dates are stored internally as double precision numbers and it is those numbers that are being compared rather than strings.

However, if you build SQL in VBA and you use a variable as a date and your standard date format is not mm/dd/yyyy, then you need to format the date.
strSQL = " Select .. From ... Where StartDT >= #" & Format(Me.TxtStartDate, "yyyy\/mm\/dd") & "#"
 

Users who are viewing this thread

Top Bottom