ACCESS FEED: Re: [MS_AccessPros] updating date fields

Jumat, 29 Maret 2013

Re: [MS_AccessPros] updating date fields

John,
The Sunday formula seems to be working so far, but the Wednesday formula is giving me a data mismatch error. I don't know how you are able to figure these formulas out as it's way beyond my capability.

Russ

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Ooops. This returns the first Sunday:
>
>
>
> IIf(WeekDay(DateSerial(Year(Date()), Month(Date()) + 1, 1)) = 1,
> DateSerial(Year(Date()), Month(Date()) + 1, 1), DateSerial(Year(Date()),
> Month(Date()) + 1, 1) + 8 - WeekDay(DateSerial(Year(Date()), Month(Date()) +
> 1, 1)))
>
>
>
> That works for every month in 2013.
>
>
>
> First Wednesday is a bit trickier:
>
>
>
> IIf(Weekday(DateSerial(Year(Date()), Month(Date()) + 1, 1)) > 4,
> DateSerial(Year(Date()), Month(Date()) + 1, 1) + 11 -
> Weekday(DateSerial(Year(Date()), Month(Date()) + 1, 1)),
> DateSerial(Year(Date()), Month(datToday) + 1, 1) + 4 -
> Weekday(DateSerial(Year(Date()), Month(Date()) + 1, 1)))
>
>
>
> To get 2nd, 3rd, etc. dates in a month, simply add 7, 14, etc.
>
>
>
> John Viescas, Author
>
> Microsoft Access 2010 Inside Out
>
> Microsoft Access 2007 Inside Out
>
> Microsoft Access 2003 Inside Out
>
> Building Microsoft Access Applications
>
> SQL Queries for Mere Mortals
>
> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ghsclass65
> Sent: Thursday, March 28, 2013 11:09 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] updating date fields
>
>
>
>
>
>
> John,
>
> That formula returns the first Saturday of the next month except when
> the current month of this year is August, then it returns the first
> Sunday in September which is Sept 1.
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
> <JohnV@>
> wrote:
> >
> > Russ-
> >
> >
> >
> > It makes me crazy that you're not including the entire thread in your
> post.
> >
> >
> >
> > The correct formula for the first Sunday of next month is:
> >
> >
> >
> > IIf(WeekDay(DateSerial(Year(Date()), Month(Date()) + 1, 1)) = 1,
> > DateSerial(Year(Date()), Month(Date()) + 1, 1),
> DateSerial(Year(Date()),
> > Month(Date()) + 1, 1) + 7 - WeekDay(DateSerial(Year(Date()),
> Month(Date())
> > + 1, 1)))
> >
> >
> >
> > Work from there to figure out the other Sundays and Wednesdays. The
> trick
> > is if the First day of the month is a Sunday or a Wednesday, you need
> to use
> > it rather than adjusting.
> >
> >
> >
> > John Viescas, Author
> >
> > Microsoft Access 2010 Inside Out
> >
> > Microsoft Access 2007 Inside Out
> >
> > Microsoft Access 2003 Inside Out
> >
> > Building Microsoft Access Applications
> >
> > SQL Queries for Mere Mortals
> >
> > http://www.viescas.com/
> >
> > (Paris, France)
> >
> >
> >
> >
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> ghsclass65
> > Sent: Thursday, March 28, 2013 9:24 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: Re: [MS_AccessPros] updating date fields
> >
> >
> >
> >
> >
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas
> > JohnV@ wrote:
> > >
> > > Russ-
> > >
> > > It's not clear what it is you're trying to do.
> > >
> > >
> > > Please explain what each calculation should yield based on today's
> date.
> > >
> > > John Viescas, Author
> > >
> > >
> > >
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
> ghsclass65
> > > Sent: Thursday, March 28, 2013 4:25 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: [MS_AccessPros] updating date fields
> > >
> > >
> > Based upon today's date of any day in March, the queries are doing
> perfectly
> > by picking the dates of the Sundays and the Wednesdays of the next
> month of
> > April. Now, change your computer system date to some day in May so
> either
> > one of the queries (they are both test queries) "should" update the
> table to
> > dates of Sundays and Wednesdays in June. Hmmm... they do not. I would
> like
> > that to happen but do not know how to do it.
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>