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-

Do the users want a specific date range? If you predefine a table (tblDates)
with one field (DateValue), you can write code to fill it:

Public Sub GenWeekDates()
Dim db As DAO.Database, rst As DAO.Recordset, datValue As Date, intI As Integer
' Point to this database
Set db = CurrentDb
' Empty the table
db.Execute "DELETE * FROM tblDates", dbFailOnError
' Set the first date = today
datValue = Date
' Open the table
Set rst = db.OpenRecordset("tblDates", dbOpenDynaset, dbAppendOnly)
' Do 3 years (approx.)
For intI = 1 To 1000
' Skip Saturday and Sunday
If WeekDay(datValue) <> 1 And WeekDay(datValue) <> 7 Then
' Add a record
rst.AddNew
' Set the date
rst!DateValue = datValue
' Save it
rst.Update
End If
' Get the next date
datValue = datValue + 1
Next intI
' Close out
rst.Close
Set rst = Nothing
Set db = Nothing
End Function

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of luvmymelody
Sent: Thursday, June 30, 2011 7:45 PM
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

------------------------------------

Yahoo! Groups Links