ACCESS FEED: [MS_AccessPros] Re: DateSerial

John,

I do check Help when I run into probems. However, at work we have a great deal of security limitations that limit our ability to use the Help.

v/r,
James Eggert

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> James-
>
> No, that's correct.
>
> =DateSerial(Year([FiscalPeriodStartDate]) ,Int(( Month([FiscalPeriodStartDate])
> -1) /3)*3+1,1)
>
> You will get an error if the field [FiscalPeriodStartDate] is null or does not
> contain a valid date/time value.
>
> Don't you look at Help before "trying" different syntax? The parameters for
> DateSerial and Int do not accept a string like "yyyy" or "mm".
>
> 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: Friday, April 13, 2012 8:20 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: DateSerial
>
>
>
> 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
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>