ACCESS FEED: RE: [MS_AccessPros] Generate table with dates in a specific month excluding weekends

Kamis, 30 Juni 2011

RE: [MS_AccessPros] Generate table with dates in a specific month excluding weekends

Jim

Why would you store dates in a table?

But here is a way

Function FillWorkDates(intMonth As Integer, intYear As Integer)

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim strSQL As String

Dim intNumOfDays As Integer

Dim x As Integer

'Get number of days in the month.

intNumOfDays = Day(DateSerial(intYear, intMonth + 1, 0))

Set db = CurrentDb

'Open an empty recordset on MyTable

strSQL = "SELECT * " _

& "FROM MyTable " _

& "WHERE 1-2"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With rs

For x = 1 To intNumOfDays

.AddNew

If Weekday(DateSerial(intYear, intMonth, x)) <> vbSaturday _

And Weekday(DateSerial(intYear, intMonth, x)) <> vbSunday Then

!WorkDate = DateSerial(intYear, intMonth, x)

.Update

End If

Next

End With

Set rs = Nothing

Set db = Nothing

MsgBox "All Done."

End Function

Regards,

Bill Mosca,

Founder, MS_Access_Professionals

That'll do IT <http://www.thatlldoit.com/> http://thatlldoit.com

MS Access MVP

<http://mvp.support.microsoft.com/profile/Bill.Mosca>
http://mvp.support.microsoft.com/profile/Bill.Mosca

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of luvmymelody
Sent: Thursday, June 30, 2011 10:45 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Generate table with dates in a specific month excluding
weekends

Hello All,

Is there a way to generate a table with all the dates in a month excluding
weekends?

Currently users are using excel to generate the table. The auto-fill is quick.
The saturday and sunday dates are deleted pretty quickly. Then I use that
spreadsheet as a linked table. But there has to be a quicker way in Access to
get the dates.

Thank You

Jim Wagner

[Non-text portions of this message have been removed]