ACCESS FEED: Re: [MS_AccessPros] updating date fields

John,

If I understand this correctly, I'll need to use different update queries if the first day of the next month falls on a Thursday, Friday or Saturday?

Russ

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Russ-
>
>
>
> I copied the expression from a VBA routine I wrote to test it and forgot to
> replace the variable name with Date(). Try this:
>
>
>
> 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(Date()) + 1, 1) + 4 -
> Weekday(DateSerial(Year(Date()), Month(Date()) + 1, 1)))
>
>
>
> Basically, I laid out a chart of the WeekDay numbers and figured out what
> needed to be added to get to the "next" Sunday or Wednesday. For Wednesday,
> the chart looks like:
>
>
>
> First Day
>
> of Month: Add this:
>
> 1 3
>
> 2 2
>
> 3 1
>
> 4 0
>
> 5 6
>
> 6 5
>
> 7 4
>
>
>
> So on Sunday, WeekDay(Date) is 1, and 4 - 1 = the 3 we need to add
>
> Monday, WeekDay(Date) is 2, and 4 - 2 = 2
>
> .. and so on.
>
>
>
> The tricky part is when the first day is Thursday to Saturday - so the
> "base" number needs to be +7 or 11.
>
>
>
> 11 - WeekDay(Thursday) = 6
>
> 11 - WeekDay(Friday) = 5
>
> .. and so on.
>
>
>
> 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: Friday, March 29, 2013 3:23 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: 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
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of ghsclass65
> > Sent: Thursday, March 28, 2013 11:09 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.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>
> > <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%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 9:24 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.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>
> > > <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%40yahoogroups.com>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.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>
> > > <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]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>