[MS_AccessPros] Re: DateSerial

 

Thanks so much for your help! Looking at the expression it now makes sense.

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
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (16)

Recent Activity:

• New Members 3
• New Files 1

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___