ACCESS FEED: Re: [MS_AccessPros] updating date fields

Kamis, 28 Maret 2013

Re: [MS_AccessPros] updating date fields

Sorry to make you crazy... need a psychologist recomendation? :-)

This formula returns the first the first Saturday of June and other months. How might I modify it to return the first Sunday and/or the first Wednesday of the next month?

--- In MS_Access_Professionals@yahoogroups.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@yahoogroups.com] On Behalf Of ghsclass65
> Sent: Thursday, March 28, 2013 9:24 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] updating date fields
>
>
>
>
>
>
>
> --- In MS_Access_Professionals@yahoogroups.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@yahoogroups.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>
> > 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]
>