Jim-
It would be easy to modify my code to figure out and start with the first day of the next month and generate a table with all the weekdays for that month.
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 Jim Wagner
Sent: Thursday, June 30, 2011 9:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Generate table with dates in a specific month excluding weekends
John,
The users need to have the table for a report. they print log sheets for each
day of the week for golf carts. the log sheets are used by staff to check out
the golf carts. the admins of the log sheets print the sheets for the coming
month.
Jim Wagner
________________________________
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thu, June 30, 2011 11:53:08 AM
Subject: 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
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar