Overlapping Dates and Filtering Records

[JackD]

My form has a series of text/combo boxes that allows filtering of staff records on criteria such as name, department, the first day of absence, the last day of absence, reason for absence and so on.

I need to be able to show the absences for a department during a monthly period. I can set the first day and last day of the filter to be the first and last day of the required month on the form and filter out the records whose first day and last day of absence falls between the beginning and end of the month. I can then pass these records off to reports or whatever afterwards. Great, I thought!

However it all goes wrong when this happens.

For example

John is absent from 29th August 2008 and his last day of absence was 5th September 2008-10-15

I want to filter absences that happened during September.

I set my filter to be 1st September 2008 and 30th September 2008. (First and last days of that month)

All records whose first and last day of absence happened during that time are filtered out. Marvellous.

However, John’s record is not included in the filter because his first day of absence happened before the 1st of September, yet he was actually absent for 5 days during September until he returned on the 5th.

Can anyone offer me some advice on how to change modify or add to the VBA (Thanks to Allen Browne) I’ve attached below that will capture these records whose dates don’t completely fit the criteria?

Also, how can I express in the code to include a record if the last day of absence date is null because the person is still to return. I think this would help me capture people who have been off for very long periods of time, so they would be picked up every subsequent month in the report and counted as an absentee until they finally return.

I have tried several twists and turns with the VBA but I am getting nowhere with it and the frustration / despair level is rising by the hour.

*********************************************************************
Private Sub cmdFilter_Click()

Dim strDateField As String
Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

‘First Day Of Absence
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([startdate] >= " & Format(Me.txtStartDate, strcJetDate) & ") AND "

End If

‘Last Day Of Absence
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([enddate] < " & Format(Me.txtEndDate + 1, strcJetDate) & ") AND "

End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else

strWhere = Left$(strWhere, lngLen)

.
Me.Filter = strWhere
Me.FilterOn = True

End If


End Sub

[FONT=&quot]*********************************************************************[/FONT]

 

[micks55]

I am thinking that there are four possibilities.
a) Started before or on first day of period and returned during period
b) Started before or on first day of period and have not returned
c) Started in the period and returned in or on last day of period
d) Started in the period and have not returned
From that I get to.
a) FirstDayOut <= Start AND LastDayOut <= End
b) FirstDayOut <= Start AND LastDayOut = Null
c) FirstDayOut > Start AND LastDayOut <= End
d) FirstDayOut > Start AND LastDayOut = Null
From that I get a plan in my head to create a continuous form based on a recordset using those criteria with the Start and End controls in the header.
All just theory and untried so it may need more tweaking but I always find that it helps to write the possible outcomes down first and I hope that it at least sets you off in the right direction. Mike

 

[Brianwarnock]

The records you want are
1 Firstday or lastday in the period
2 Firstday < start of period and last day >=start of period or null

Brian

 

[JackD]

Thank you for your suggestions and assistance, but I am still having difficulty in getting the VBA expressions correct.

I've attached the DB with a few sample records to look at with dates that start and end in october, dates that start before October but end within it and records that have a start date insde October but and end date sometime in the future.

I'd be most grateful if someone could take a look at this for me and modify the code to capture Brian and Micks solution to my problem outlined in my original post. My level of expertise and experience is obviously not enough for me to solve this on my own.

Kind regards

 

[Brianwarnock]

I haven't got time at the moment to get into your db in detail but wrote a simple query to select records to test my coding and the where clause is

WHERE (((monitoring.startdate) Between periodstart And periodend)) OR (((monitoring.enddate) Between periodstart And periodend)) OR (((monitoring.startdate)<periodstart) AND ((monitoring.enddate) Is Null Or (monitoring.enddate)>periodend));

Hope this helps

Brian

 

[JackD]

Thanks for your prompting and input. Sadly I am stilll unable to get the results I need.

I have tried to express the VB code to capture Brian's SQL (see below), but I have failed dismally.

Anyone out there who can offer me a shoulder to cry on or maybe a hand out of this quick drying cement that is way above my head.


*************************************************
Private Sub cmdFilter_Click()

Dim strDateField As String
Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " ([enddate])<= " & Format(Me.txtEndDate, conJetDate) & ") AND "
End If

'Start date, but no End.
If IsNull(Me.txtStartDate) Then strWhere = strWhere & " ([startdate]) >= " & Format(Me.txtStartDate, strcJetDate) & ") AND "
End If

'Both start and end dates.
strWhere = strWhere & " Between " & Format(Me.txtStartDate, conJetDate) _
& " And " & Format(Me.txtEndDate, strcJetDate) & ") AND "
End If

End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else

strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True

End If

End Sub

 

[apr pillai]

Can we try a different approach with a Query and a Form built on that Query. I don't know whether this is acceptable.

We are trying to find out the absence within the month. May be the start date is from previous month or some where at the end of the month under review. In both cases the startdate and enddate fields will be empty. In that case startdate can be taken as the first day of the month on the Query Column with the expression (assuming current month under review):

 SD: IIf(IsNull([startdate]) Or [startdate]=0,DateSerial(Year(Date()),Month(Date()),1),[startdate])

and if the last date is empty take the last date of the month under review with the expression (assuming current month):

 ED: IIf(IsNull([enddate]) Or [enddate]=0,DateAdd("m",1,DateSerial(Year(Date()),Month(Date()),1))-1,[enddate])

You can use a parameter table with Start_date and End_date values on the Query and re-write the lengthy expressions given above with the parameter date on the table (which you are using to filter the data).

Create a separate Form on the Query and Filter the data with your code. If his absence started from previous month it will figure the number of days he was absent during that month too.

 

[JackD]

Almost there, in a glass half full kinda way! Still having issues though.

If you set a date period from the 1st oct to the 31st you can see the filtered list picks up the correct records, including the ones with dates that lap inside and outside of the first/last day of month range, as described above. However if you go and try to add an additional filter..Service for example, I get the syntax error and the list of records doesn't change. If you reset the form and try to add another filter on it's own, I get the syntax error again.

1 thing fixed, broke the rest...

Attached latest version for perusal / amending or any advice.

Cheers

 

[Brianwarnock]

Hi Jack
First to get your DB to run I had to comment out the line indicated below.

Private Sub cmdOk_Click()
On Error Resume Next
    'Purpose:   Transfer the result back to the calling text box (if there is one), and close.
    
    If gtxtCalTarget = Me.txtDate Then
        'do nothing
    Else
        gtxtCalTarget = Me.txtDate
    End If
    [COLOR="Red"]'gtxtCalTarget.SetFocus   BJW[/COLOR]
    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

I have appended BJW to all the changes I have made so that you can find them.

You needed to place () around all of the date conditions as they represented one AND, if you see what I mean.
I have done this and my coding appears to work, but please give it a thorough testing and by almeans change if you prefer to do it differently.

Brian

 

[JackD]

Would have responded much sooner but I've had a couple weeks off work in an attempt to regain some sanity, but I think I'll need more than 2 weeks to work on that....

I'd like to thank everyone for their help with this database I am trying to create, especially Brian who has taken me to the cusp of salvation.

I have made a few changes here and there to the form and have restricted any searches being carried out until both the Start and End dates have a value in them, and have made changes to the coding that has been suggested.

Unfortunately there are still a couple of issues that I have been unable to correct.

When the date parameters are set, eg. Start date 1st September End date 30th September the resulting filtered list does not include periods of absence that lap from one month into another. You can see this if you set the start date to 1st August and end date as the 30th September. Record 8 (vivien) shows that she has been off since 11th August and is yet to return to work. Record 10 (susan) shows that she was off from 11th August and returned on the 9th September. If you glance down the list you will see other instances of this too.

Ideally these records would have been included in the results as they have had absences that occurred during September, even though they originated before the beginning of September.

In addition to this, you can see at the bottom of the list 4 records whose first day of absence does not occur until sometime in October. Ideally these should not appear in the list as those folks have had no absence period at all in September.

I thought this issue had been resolved as per previous posts in this thread, however it seems to have resurfaced somewhere along the way and I cannot determine how to correct it.

I have attached the conundrum to this post with some sample data. In the meantime I will retreat to a safe distance so my cries do not disturb the other patients in the Safety Room while I wait for a saviour.

 

[Brianwarnock]

Jack I took out all of your date criteria building code and put in this

   strWhere = strWhere & "(([startdate] BETWEEN " & Format(Me.txtStartDate, strcJetDate) & " AND "
    strWhere = strWhere & Format(Me.txtEndDate, strcJetDate) & ") Or "
    strWhere = strWhere & "([enddate] BETWEEN " & Format(Me.txtStartDate, strcJetDate) & " AND "
    strWhere = strWhere & Format(Me.txtEndDate, strcJetDate) & ") Or "
    strWhere = strWhere & "([startdate] < " & Format(Me.txtStartDate, strcJetDate) & " AND "
    strWhere = strWhere & "([enddate] is null OR [enddate] > " & Format(Me.txtEndDate, strcJetDate) & ")) And "

This was to match my SQL criteria that I had run in a simple test db

'WHERE (((monitoring.startdate) Between periodstart And periodend))
'OR (((monitoring.enddate) Between periodstart And periodend))
'OR (((monitoring.startdate)<periodstart) AND ((monitoring.enddate) Is Null Or (monitoring.enddate)>periodend));

I think that it now works.

Note I had errors of various sorts to do with your bookmark Ialso changed this as the If seemed superfluous and had to comment out the Set focus.

'If gtxtCalTarget = Me.txtDate Then
'do nothing
'Else
gtxtCalTarget = Me.txtDate
'End If
'gtxtCalTarget.SetFocus

Hope the above helps

Brian

 

[micks55]

Jack,
I had a look at your file and it's way over my head. I go about things in a far simpler fashion so I expect you will think the attached is really lame. Sorry but I can't raise any enthusiasm for learning the clever stuff, I just look for solutions that work for me.
The attached is not the solution you asked for but it works (I think) and you can grab the "where" syntax from the sql of the query.
Hope it helps. Mick.

 

[Brianwarnock]

Hi Jack
after i had logged off last night, GMT, it struck me that as the date criteria was mandatory we could simplify the code a little as we did not need to mess with the AND at the end. See new module below.

Brian

Private Sub cmdFilter_Click()
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        Until the Date Criteria which must exist. Then we tidy up in the last condition.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
      ' E.G. WHERE (((monitoring.startdate) Between periodstart And periodend))
   'OR (((monitoring.enddate) Between periodstart And periodend))
   'OR (((monitoring.startdate)<periodstart) AND ((monitoring.enddate) Is Null Or (monitoring.enddate)>periodend));
    
    Dim strDateField As String
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Dim periodstart
    Dim periodend
    'Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    periodstart = Format(Me.txtStartDate, strcJetDate)
    periodend = Format(Me.txtEndDate, strcJetDate)
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
   
 If IsNull(Me.txtStartDate) Then
   MsgBox "You Must Enter a Start Date!", vbInformation, "Missing Criteria."
   Me.cmdfilter.SetFocus
   
   End
   
  ElseIf IsNull(Me.txtEndDate) Then
   MsgBox "You Must Enter an End Date!", vbInformation, "Missing Criteria."
   Me.cmdfilter.SetFocus
   End
   
  'ElseIf IsNull(Me.City) Then
   'MsgBox "You Must Enter a City!"
   'Cancel = True
   'Me.City.SetFocus
   'ElseIf IsNull(Me.State) Then
   'MsgBox "You Must Enter a State!"
   'Cancel = True
   'Me.State.SetFocus
  End If
   
      
    'Find Employee Name
    If Not IsNull(Me.txtFilterMainName) Then
        strWhere = strWhere & "([EmployeeName] Like ""*" & Me.txtFilterMainName & "*"") AND "
    End If
    
    ' Name Of Service
     If Not IsNull(Me.cboFilterService) Then
        strWhere = strWhere & "([ServiceName] = """ & Me.cboFilterService & """) AND "
    End If
    
    
    'Back to work paperwork, interview and councelling
    'Return To Work Paperwork Received
    If Me.cboRTW_PWR = -1 Then
        strWhere = strWhere & "([RTWI_PWR] = True) AND "
    ElseIf Me.cboRTW_PWR = 0 Then
        strWhere = strWhere & "([RTWI_PWR] = False) AND "
    End If
    
    'Return To Work Interview Carried or Not carried out
       If Me.cboRTWI_NCO = -1 Then
        strWhere = strWhere & "([RTWI_NCO] = True) AND "
    ElseIf Me.cboRTWI_NCO = 0 Then
        strWhere = strWhere & "([RTWI_NCO] = False) AND "
    End If
    
    ' Counselling Paperwork Received
       If Me.cboCPR = -1 Then
        strWhere = strWhere & "([CPR] = True) AND "
    ElseIf Me.cboCPR = 0 Then
        strWhere = strWhere & "([CPR] = False) AND "
    End If
   
 ' Build date search criteria
    strWhere = strWhere & "(([startdate] BETWEEN " & periodstart & " AND " & periodend & ")"
    strWhere = strWhere & "Or ([enddate] BETWEEN " & periodstart & " AND" & periodend & ")"
    strWhere = strWhere & "Or ([startdate] < " & periodstart & " AND "
    strWhere = strWhere & "([enddate] is null OR [enddate] > " & periodend & "))) "
    
   
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
        
       ' Me!txtStartDate.SetFocus
    
       If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox "Nothing matches your request, try again", vbInformation, "Zero Search Result."
    
    End If
      

End Sub

 

[JackD]

There are times in your life when you just have to stand back and applaud in sheer bewilderment and acknowledgement of a tremendous event or person.

Mr Warnock, I am eternally grateful for your time, generosity and genuis.

You have truly dug me out of a hole from which I thought I was never going to be get out of.

Thanks of course, to everyone else who contributed and gave me food for thought with your helpful suggestions.

I will return to this thread in due course and post my completed dbase (without any meaningful data in it of course!) so that others can make use of and learn from Brian's teachings!

Thanks again,

Jack

 

[Brianwarnock]

Thanks Jack,
responses like that truly make the effort worthwhile, you would be amazed at how many posters don't even let you know that your suggestions work!

Brian