[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 (14)

Recent Activity:

• New Members 3
• New Files 1

Visit Your Group

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

.

__,_._,___