ACCESS FEED: RE: [MS_AccessPros] updating date fields

Russ-

You need the OTHER expression for 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())

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 8:03 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] updating date fields

John,

I gave it a good test. Seems to be working fine until it comes to a
month where the first day of the month is Sunday such as happens in
September and December of this year. Then the update query sets the
table values to the last Sunday date of the current month. For example,
if I set my system date to September, and run the query, I get the
following results in the table for October:

S1 - 9/29/2013

S2 - 10/6/2013

S3 - 10/13/2013

S4 - 10/20/2013

S4 - 10/27/2013

The formula I'm using for that first week is:

IIf(Weekday(DateSerial(Year(Date()),Month(Date())+1,1))>4,DateSerial(Yea\
r(Date()),Month(Date())+1,1)+8-Weekday(DateSerial(Year(Date()),Month(Dat\
e())+1,1)),DateSerial(Year(Date()),Month(Date())+1,1)+1-Weekday(DateSeri\
al(Year(Date()),Month(Date())+1,1)))

Wednesdays so far look ok.

Russ

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

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