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]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (10) |
Tidak ada komentar:
Posting Komentar