Jim,
Apparently, you wish to generate all dates in next month (excluding week ends). Sample query Q_DatesNextMonthExclWkEnds as given below, represents a pure SQL based solution, that should get you the desired results. Table T_Ref has a single field RefNum (number type) populated with sequential numbers from 0 to 30.
Q_DatesNextMonthExclWkEnds
==============================
SELECT DateAdd("m",1,Date()-Day(Date())+1)+[RefNum] AS Dte
FROM T_Ref
WHERE (((Weekday(DateAdd("m",1,Date()-Day(Date())+1)+[RefNum])) Not In (1,7)) AND ((Month(DateAdd("m",1,Date()-Day(Date())+1)+[RefNum]))=Month(DateAdd("m",1,Date()-Day(Date())+1))));
==============================
If at any stage, it becomes necessary to generate a similar date list for the current month, sample query Q_DatesThisMonthExclWkEnds as given below, could be used:
Q_DatesThisMonthExclWkEnds
==============================
SELECT Date()-Day(Date())+1+[RefNum] AS Dte
FROM T_Ref
WHERE (((Weekday(Date()-Day(Date())+1+[RefNum])) Not In (1,7)) AND ((Month(Date()-Day(Date())+1+[RefNum]))=Month(Date())));
==============================
Best wishes,
A.D. Tejpal
------------
----- Original Message -----
From: Jim Wagner
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, July 01, 2011 02:30
Subject: Re: [MS_AccessPros] Generate table with dates in a specific month excluding weekends
I use a query as the source for the report. I just added a parameter for the
users.
Thank you everyone for your help.
Jim Wagner
________________________________
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thu, June 30, 2011 1:18:15 PM
Subject: RE: [MS_AccessPros] Generate table with dates in a specific month
excluding weekends
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
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar