Thanks Bill! - for your kind words. So nice of you.
Quite often, SQL based solution offers an interesting alternative (and fun to explore).
Best wishes,
A.D. Tejpal
------------
----- Original Message -----
From: Bill Mosca
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, July 01, 2011 19:53
Subject: Re: [MS_AccessPros] Generate table with dates in a specific month excluding weekends
A.D.
I KNEW you'd have a query to do this! This is the smartest method seeing there is no need to store the dates in a table.
Thanks for sharing another one of your clever solutions with us.
Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile/Bill.Mosca
--- In MS_Access_Professionals@yahoogroups.com, "A.D. Tejpal" <adtp@...> wrote:
>
> 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@...>
> 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@...>
> 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