ACCESS FEED: Re: [MS_AccessPros] updating date fields

John,

That got it! Thank you, thank you, thank you!
Looks like I'll have to buy a couple of your books John.

Russ

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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]
>