Minggu, 03 Juli 2011

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

 

Hi A.D.,

You are most welcome.

Thank you for posting these functions. They are shorter what I have been using and will make my code easier to read.

Kind Regards,
Pat Wood

--- In MS_Access_Professionals@yahoogroups.com, "A.D. Tejpal" <adtp@...> wrote:
>
> Hi Pat,
>
> Thanks for the kind compliments! It is really nice on your part to draw attention to the extraneous character that appears to have been inserted by Yahoo.
>
> While on the subject, some of the compact functions that could be useful in getting the first and last days of current month (i.e. the month pertaining to Dte argument), previous and next months, are also placed below for ready reference:
>
> ' Code in general module
> '================================
> Function Fn_FirstDayThisMonth(Dte As Date) As Date
> Fn_FirstDayThisMonth = Dte - Day(Dte) + 1
> End Function
> '------------------------------------------------
>
> Function Fn_LastDayThisMonth(Dte As Date) As Date
> Fn_LastDayThisMonth = _
> DateAdd("m", 1, Dte - Day(Dte) + 1) - 1
> End Function
> '------------------------------------------------
>
> Function Fn_FirstDayPrevMonth(Dte As Date) As Date
> Fn_FirstDayPrevMonth = _
> DateAdd("m", -1, Dte - Day(Dte) + 1)
> End Function
> '------------------------------------------------
>
> Function Fn_LastDayPrevMonth(Dte As Date) As Date
> Fn_LastDayPrevMonth = Dte - Day(Dte)
> End Function
> '------------------------------------------------
>
> Function Fn_FirstDayNextMonth(Dte As Date) As Date
> Fn_FirstDayNextMonth = _
> DateAdd("m", 1, Dte - Day(Dte) + 1)
> End Function
> '------------------------------------------------
>
> Function Fn_LastDayNextMonth(Dte As Date) As Date
> Fn_LastDayNextMonth = _
> DateAdd("m", 2, Dte - Day(Dte) + 1) - 1
> End Function
> '================================
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> ----- Original Message -----
> From: patrickawood
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Sunday, July 03, 2011 09:06
> Subject: Re: [MS_AccessPros] Generate table with dates in a specific month excluding weekends
>
>
> Hi A.D.,
>
> You are just amazing! I liked your queries so much I have saved them in a safe place and I modified one to get all the dates in last month excluding weekends.
>
> For the sake of others who may want to use the queries I want to make them aware of a extra character that causes one of the queries to fail. It apparently was added by Yahoo at the line break in the query text because it does not show up as I look at it in this reply post but it does in all the other posts. Strange.
>
> The extra character is \ and it is found in the Q_DatesNextMonthExclWkEnds query near the end.
>
> ...Month(DateAdd("m",1,Date(\ <--There it is!
> )-Day(Date())+1))));
>
> Just remove that bad character and the query runs great.
>
> Thank you, A.D. for another great Access post.
>
> Warm Regards,
> Pat Wood
>
> --- 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