ACCESS FEED: RE: [MS_AccessPros] updating date fields

Russ-

No, the expression takes care of that. That's why this part:
IIf(Weekday(DateSerial(Year(Date()), Month(Date()) + 1, 1)) > 4, is there to
see if you need to use 4 or 11 as the base for the calculation.

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 6:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
<mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of ghsclass65
> Sent: Friday, March 29, 2013 3:23 PM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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>
> <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%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 11:09 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
> >
> >
> >
> >
> >
> >
> > 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>
> > <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%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 9:24 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: 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>
> > > <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%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>
> > > <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>
> > > <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]
>

[Non-text portions of this message have been removed]