Form Filters and date ranges

[JackDani]

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 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 day!

Here is part of the VB my form is happily working with, applying the filters and return records nicely, minus the not picking up certain instances as described above

Thanks for reading.

*********************************************************************
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

************************************************** *******************

 

[JBinQLD]

G'day JackDan,

I amended one line of your code and added two line. See revised section below:

'Last Day Of Absence
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([enddate] < " & Format(Me.txtEndDate, strcJetDate) & ") [red]OR[/red] "

End If
[red]
'Check for entries that have a start date but no end date (eg., ongoing leave)

strWhere = strWhere & "(isdate([startdate]) AND isnull([enddate])) OR "

'Check for leave that has ended within the chosen range regardless of when it started.

strWhere = strWhere & "([enddate] BETWEEN " & Format(Me.txtStartDate, strcJetDate) & " AND " & Format(Me.txtEndDate, strcJetDate) & ") OR  "
[/red]

Notice the final ") OR " has two spaces at the end to make up for the fact the word OR is one char shorter than AND so the correct bit gets sliced off by your Left statement later....

Take it easy,

JB

 

[JackDani]

Hey JB,

many thanks for your response and solution! I've placed your code into my form but Access is popping up a

syntax error (missing operator) in query or expression '(isdate[startdate]) AND isnull (enddate]) OR ([enddate] Between AND )'

If you get a chance to take a look I have attached the database with your code added.

Cheers

 

[JBinQLD]

G'day fella,

I get your error if i don't fill in from and to date. If i fill both as per your example it works fine?

if you expect users to leave either of them blank a quick and dirty fix in the code is to use somethin like

dteMyDate=nz(me!StartDate,#1/1/01#)

which will set the variable to the entered value or an "ancient" value if left blank.

Hope helps,

JB

 

[JackDani]

Hi JB,

many thanks again for your help.

If I enter a start and end date, then select 'Bulding Services' on the combo box (or any other criteria in the header) and hit submit, the date period is picked up by your excellent coding and captures the dates, but the form seems to ignore the selection in the 'Service' box.

I was also thinking of disabling the submit button until the user has selected a date range in the 2 boxes to prevent that other syntax from occuring, or maybe put a default date range in the boxes to start with.

I really appreciate your assistance,

Cheers

 

[JBinQLD]

Jacko,

if you want that too replace my two lines with:

    'Check for entries that have a start date but no end date (eg., ongoing leave)

    strWhere = strWhere & "(([ServiceName] like '*" & Me.cboFilterService & "*') AND (isdate([startdate]) AND isnull ([enddate]))) OR "

    'Check for leave that has ended within the chosen range regardless of when it started.

    strWhere = strWhere & "(([ServiceName] like '*" & Me.cboFilterService & "*') AND ([enddate] BETWEEN " & Format(Me.txtStartDate, strcJetDate) & " AND " & Format(Me.txtEndDate, strcJetDate) & ")) OR  "

and all is sweet mate

JB