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