ACCESS FEED: [MS_AccessPros] Re: DateSerial

Kamis, 12 April 2012

[MS_AccessPros] Re: DateSerial

John,

Thanks, this is beginning to make sense. I was reading some blogs concerning date functions and some say it is better to attach these to an unbound textbox in the Control Source. Please let me know your thoughts on this.

v/r,
James Eggert

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> James-
>
> DateSerial(Year(Date), Int((y - 1) / 3) * 3 + 1, 1)
>
> Gets you the first day of the quarter.
>
> Format(DateSerial(Year(Date), Int((y - 1) / 3) * 3 + 1, 1), "q")
>
> Gets you the quarter number in a "normal" calendar.
>
> Mod(CInt(Format(DateSerial(Year(Date), Int((y - 1) / 3) * 3 + 1, 1), "q")), 4) +
> 1
>
> Gets you your quarter number with the first quarter starting October 1.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> 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 j1eggert
> Sent: Thursday, April 12, 2012 2:32 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: DateSerial
>
>
> Clive/John,
>
> Thank you both for your help! One thing I did forget to mention is when our
> Fiscal Year starts, which is October 1, which means this is quarter 3. What
> would be the best approach for starting the first quarter at October 1, 2011?
>
> v/r,
> James Eggert
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> >
> > Clive-
> >
> > It looks just fine to me:
> >
> > For y=1 to 12:Debug.Print y:Debug.Print DateSerial(Year(Date), Int((y - 1) /
> 3)
> > * 3 + 1, 1):next y
> > 1
> > 1/1/2012
> > 2
> > 1/1/2012
> > 3
> > 1/1/2012
> > 4
> > 4/1/2012
> > 5
> > 4/1/2012
> > 6
> > 4/1/2012
> > 7
> > 7/1/2012
> > 8
> > 7/1/2012
> > 9
> > 7/1/2012
> > 10
> > 10/1/2012
> > 11
> > 10/1/2012
> > 12
> > 10/1/2012
> >
> > Int((month - 1) / 3) * 3 "rounds down" the current month to the last month of
> > the previous quarter, then you add 1 to get the first month of current
> quarter.
> >
> > Int((4 - 1) / 3) = 1
> > Int((5 -1) / 3) = 1
> > Int((6 - 1) / 3) = 1
> >
> > Remember, Int truncates - it doesn't round.
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > 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 Clive
> > Sent: Thursday, April 12, 2012 2:55 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: DateSerial
> >
> >
> > Hi James,
> >
> > No prize yet. Your function will always return the
> > first day of the current month.
> > You are subtracting 1, dividing by 3,
> > multiplying by 3 and adding 1.
> > That gives you the number you first thought of.
> >
> > Try this in the Debug Window first,
> > For y=1 to 12:?y;tab(7);DateSerial(YearDate), Mid$("111222333444",y,1),1):next
> y
> >
> > Regards, Clive.
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > >
> > > James-
> > >
> > > That looks correct.
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > 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 j1eggert
> > > Sent: Wednesday, April 11, 2012 7:23 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Re: DateSerial
> > >
> > >
> > > John,
> > >
> > > I think this is what I should have done:
> > >
> > > FirstDay = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1)
> > >
> > > Please let me know if this is correct.
> > >
> > > Thanks,
> > > James Eggert
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > > >
> > > > James-
> > > >
> > > > Looks like the problem is here:
> > > >
> > > > Int ((month(Date())+)/3)*3+1
> > > >
> > > > What did you mean to add to Date() inside the Month function?
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > 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 j1eggert
> > > > Sent: Wednesday, April 11, 2012 4:50 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] DateSerial
> > > >
> > > >
> > > > I am using Access 2007 and tried to plug the following vba code into the
> > > Control
> > > > Source of a text box, which results in an error:
> > > >
> > > > =DateSerial(Year(Date()), Int ((month(Date())+)/3)*3+1,1)
> > > >
> > > > If some one could point out my error I would really appreciate it.
> > > >
> > > > v/r,
> > > > James Eggert
> > > >
> > >
> >
>