ACCESS FEED: [MS_AccessPros] Re: DateSerial

Duane,

I appreciate the help, but the textboxes are bound and I need them to update the fields in a table. I put the "yyyy" and "mm" because some of the examples I found on the internet use these types of parameters. In addition, I am placing the expression in the control source of a textbox. I hope this helps.

Thanks,
James Eggert

--- In MS_Access_Professionals@yahoogroups.com, "Duane" <duanehookom@...> wrote:
>
> I'm not sure why you added all the "yyyy" and "mm". I pasted John's expression into the debug window (press Ctrl+G) and inserted an actual date value and it returned the first day of the quarter:
>
> ?DateSerial(Year(#7/2/2012#),Int((Month(#7/2/2012#)-1) /3)*3+1,1)
> 7/1/2012
>
> ?DateSerial(Year(#11/11/2012#),Int((Month(#11/11/2012#)-1) /3)*3+1,1)
> 10/1/2012
>
> ?DateSerial(Year(#1/11/2012#),Int((Month(#1/11/2012#)-1) /3)*3+1,1)
> 1/1/2012
>
> Duane Hookom
> MS Access MVP
>
> --- In MS_Access_Professionals@yahoogroups.com, j1eggert <no_reply@> wrote:
> >
> >
> > Hello John,
> >
> > This should be the last question on this subject. I copied and pasted the expression you wrote into the control source, =DateSerial(Year([FiscalPeriodStartDate]),Int((Month([FiscalPeriodStartDate])-1)/3)*3+1,1), but ended up getting an error. So, I modified it with following corrections:
> >
> > =DateSerial("yyyy",([FiscalPeriodStartDate]),Int(("mm", ([FiscalPeriodStartDate])-1)/3)*3+1,1)
> >
> > =DateSerial("yyyy", [FiscalPeriodStartDate]),Int(("mm", [FiscalPeriodStartDate])-1)/3)*3+1,1)
> >
> > Neither of these work. I have been sleep deprived for the last three days and have no more brain energy left. Any help on this last hump would be greatly appreciated.
> >
> > v/r,
> > James Eggert
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > >
> > > James-
> > >
> > > Ah, you should have used the original expression, not the one I used in the
> > > demo.
> > >
> > > =DateSerial(Year([FiscalPeriodStartDate]),Int((Month([FiscalPeriodStartDate])-1)
> > > /3)*3+1,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/
> > > (Nice, France)
> > >
> > > -----------------------------------------------------
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of j1eggert
> > > Sent: Friday, April 13, 2012 1:39 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Re: DateSerial
> > >
> > >
> > >
> > > John,
> > >
> > > Sorry, I meant to include what I had tried.
> > >
> > > When I pasted the function into the textbox control source it automatically
> > > modified expression by adding brackets around "Date" and "y":
> > > =DateSerial(Year([Date]),Int(([y]-1)/3)*3+1,1). I tried the following:
> > >
> > > =DateSerial(Year([FiscalPeriodStartDate]),Int(([y]-1)/3)*3+1,1)
> > >
> > > =DateSerial("Year",[FiscalPeriodStartDate],[Date]),Int(([y]-1)/3)*3+1,1)
> > >
> > > =DateSerial(Year([Date],[FiscalPeriodStartDate]),Int(([y]-1)/3)*3+1,1)
> > >
> > > Thanks for your help in advance!
> > >
> > > v/r,
> > > James Eggert
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > > >
> > > > James-
> > > >
> > > > Please post what you've tried. It's easier to correct than create from
> > > scratch.
> > > > <s>
> > > >
> > > > 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 8:02 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Re: DateSerial
> > > >
> > > >
> > > >
> > > > John,
> > > >
> > > > I can write simple expressions in the control source of a bound textbox, but
> > > no
> > > > matter how I craft the expressions to reference the field it keeps coming up
> > > > with errors. If you would not mind using one of the expressions you provided
> > > as
> > > > an example I would appreciate it.
> > > >
> > > > v/r,
> > > > James Eggert
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > > > >
> > > > > James-
> > > > >
> > > > > If the date you need in the function is dependent on a field in the bound
> > > > Record
> > > > > Source, then yes, put it in the Control Source of a text box. If you just
> > > need
> > > > > the value for "today", calculate it in the form's Load event and assign it
> > > to
> > > > an
> > > > > unbound text box.
> > > > >
> > > > > 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 3:29 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: [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
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>