James-
That's not right. Do you at least have the local help files installed with
Access? After you get the Help window open, choose Content From This Computer
in the Search drop-down 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: Monday, April 16, 2012 7:15 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [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
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Tidak ada komentar:
Posting Komentar