Thanks for jumping in, Duane.
James- When you put an expression like that in the Control Source of a text box,
you're not assigning the result of the calculation to anything. You're simply
asking Access to display the value, and you can't update it. If you want to
assign the result to a field in your record source, you need to do it in code as
Duane suggests.
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 Duane
Sent: Friday, April 13, 2012 9:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: DateSerial
James,
If you have a very good reason to store a value that might be calcuable on the
fly, you can use code in the after update of the date textbox control. This code
can set the value of another text box that is bound to the field you want to
store the calculated field.
For instance, if you have a text box named "txtMyDate" and you want the start of
the quarter date pushed into a text box "txtStartOfQuarter", you code might look
like:
Private Sub txtMyDate_AfterUpdate()
If IsDate(Me.txtMyDate) Then
Me.txtStartOfQuarter = DateSerial(Year(Me.txtMyDate), _
Int((Month(Me.txtMyDate) - 1) / 3) * 3 + 1, 1)
End If
End Sub
Duane Hookom
MS Access MVP
--- In MS_Access_Professionals@yahoogroups.com, j1eggert <no_reply@...> wrote:
>
> 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
Tidak ada komentar:
Posting Komentar