A date range form-Populate my table with each date in the range (exclude sat/sun)! (1 Viewer)

MsLady · Sep 14, 2006

I have looked high and low over the internet for a way to do this, but can't seem to find any.

I have a form with 2comboboxes (popup calender) that takes a vacation date range (fromDate and toDate) for each employee (peopleId). This form is bound to a table (tblAbsense) with fields as peopleId, absenceDate, absenceType. After i click the submit button, I would like to make entries in my table for each date in the date range.

Eg. if i select fromDate - toDate: 9/14/06 - 9/21/06. I would like it to fill in each day absent, "only the weekdays" (excluding saturday and sunday) into my tblAbsence.
So, table should take entry like:

peopleId absenceDate absenceType
001 9/14/06 vacation
001 9/15/06 vacation
001 9/18/06 vacation
001 9/19/06 vacation
001 9/20/06 vacation
001 9/21/06 vacation

(notice i have excluded 16th and 17th in my table entries as those dates fall on weekend)?

This is what i'd like to achieve. Can anyone help me?

david.brent · Sep 14, 2006

You could check if any of the dates in the range are a Saturday or Sunday. I would use 'format' to check what day it was. Something like...

For DateRange = cdbl(DateFrom) to cdbl(DateTo) 'Changes dates to the number of days from 01/01/1900
If format(cdate(DateRange),"ddd") <>"Sat" and If format(cdate(DateRange),"ddd") <> "Sun" Then

Use a recordset to update the table tblAbsense

End If

Next DateRange

Something like this will work but I'm sure there are more elegant ways to do this. Hopefully someone will post one.

One other thing, my organisation counts weekends as sick leave if you are off ill on a Friday and the following Monday. It doesn't affect pay but does count towards a cumulative total for sick. This would mean that I would have to check absence type as well if I where doing something like this.

Take care.

Bodisathva · Sep 14, 2006

Code:

    Dim dateIDX As Date
    Dim recDates As Recordset

    Set recDates = CurrentDb.OpenRecordset("tblAbsence", dbOpenDynaset)
    For dateIDX = startDate To endDate
        If (Weekday(dateIDX) > 1 And Weekday(dateIDX) < 7) Then
          With recDates
                 .AddNew
                 .peopleID = employeeNumber
                 .absenceDate = tmpDate
                 .absenceType = typeAbsence
                 .Update
            End With
        End If
    Next dateIDX

you may want to consider creating a table of business holidays and omit them as well as weekends...

MsLady · Sep 14, 2006

Hi David and Bodisathva:
You have both been very helpful. and thanks for the quick response!!

Here's what i have now but i get "runtime error 13. type mismatch" do you guys have any idea?

Code:

Dim dateIDX As Date
    Dim recDates As Recordset
    Dim db As Database
    
    Set recDates = CurrentDb.OpenRecordset("tblAbsence", dbOpenDynaset)
    For dateIDX = CDbl(Me.fromDate) To CDbl(Me.toDate)
        If (Weekday(dateIDX) > 1 And Weekday(dateIDX) < 7) Then
          With recDates
                 .AddNew
                 .Fields("peopleId") = Me.cbopeople.Column(0)
                 .Fields("absenceDate") = Format(CDate(dateIDX)) 'tmpDate?
                 .Fields("absenceType") = Me.absenceType
                 .Update
            End With
        End If
    Next dateIDX

@ Bodisathva: you read my mind

, i have considered the business holidays too and have created a table but didn't know hwo to handle. So, I guess i'll do a join query (tblholiday & tblabsence) and base my recordset on that. then include it in the IF statement i have above?
but i can come to that later, just need to get this to work first.

thanks

Bodisathva · Sep 14, 2006

MsLady said:
Here's what i have now but i get "runtime error 13. type mismatch" do you guys have any idea?

Sounds like you have a discrepancy between the data type of a field and what you are trying to push into it...double check table field data-types against your form field data-types. IMHO....suggestions below:

Code:

    Dim dateIDX As Date
    Dim recDates As Recordset
    Dim db As Database  [COLOR="red"]'don't need this, do you?[/COLOR]
    
    Set recDates = CurrentDb.OpenRecordset("tblAbsence", dbOpenDynaset)
    For dateIDX = [COLOR="Red"]CDate[/COLOR](Me.fromDate) To [COLOR="red"]CDate[/COLOR](Me.toDate) 
[COLOR="red"]     'do you have to typecast? are the fields not already dates?[/COLOR]
        If (Weekday(dateIDX) > 1 And Weekday(dateIDX) < 7) [COLOR="Red"]AND_
           (DCount("DateField", "HolidayTable","DateField = " & dateIDX)<1)[/COLOR] Then
          With recDates
                 .AddNew
                 [COLOR="Red"]![/COLOR]peopleId = Me.cbopeople.Column(0)
                [COLOR="red"] ![/COLOR]absenceDate = dateIDX [COLOR="Red"]'no need to typecast...is a date[/COLOR]
                 [COLOR="Red"]![/COLOR]absenceType = Me.absenceType
                 .Update
            End With
        End If
    Next dateIDX

MsLady said:
@ Bodisathva: you read my mind , i have considered the business holidays too and have created a table but didn't know hwo to handle. So, I guess i'll do a join query (tblholiday & tblabsence) and base my recordset on that. then include it in the IF statement i have above?
but i can come to that later, just need to get this to work first.

see insert above

MsLady · Sep 14, 2006

mean! see how u painted the code red! lol

okay, i checked my date field, and i made ur edits as suggested

here's what i have now, works like charm

Code:

Dim dateIDX As Date
    Dim rs As Recordset
   
    Set recDates = CurrentDb.OpenRecordset("tblAbsence", dbOpenDynaset)
    For dateIDX = Me.fromDate To Me.toDate
        If (Weekday(dateIDX) > 1 And Weekday(dateIDX) < 7) Then
          With recDates
                 .AddNew
                 !peopleId = Me.cbopeople.Column(0)
                 !absenceDate = dateIDX
                 !absenceType = Me.frameAT.Value
                 .Update
            End With
        End If
    Next dateIDX

And thanks for that holiday code, i will incorporate it shortly...

Thanks buddy!

Thanks also david.brent

A date range form-Populate my table with each date in the range (exclude sat/sun)! (1 Viewer)

MsLady

Traumatized by Access

david.brent

Registered User.

Bodisathva

Registered User.

MsLady

Traumatized by Access

Bodisathva

Registered User.

MsLady

Traumatized by Access

Similar threads

Users who are viewing this thread