B
BarryS
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
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 modify or add to the VBA (Thanks to Allen Browne) I’ve attached
below that will capture those 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 failed miserably for the last week struggling to get this to work.
Please help.
************************************************** *******************
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
************************************************** *******************
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
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 modify or add to the VBA (Thanks to Allen Browne) I’ve attached
below that will capture those 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 failed miserably for the last week struggling to get this to work.
Please help.
************************************************** *******************
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
************************************************** *******************