Due Date Calculated
in [Form]
|
From: Ron A. on 6 Apr 2010 14:15 I need assistance on an expression that allows for the due date of recurring training to be one year from date completed. Details are as follows: I have a form named [frmTRNComplete] based on table named [tblTRNComplete] Form controls are: TRNID (combo box), DateComp (Text Box) , DateNext (text box), Reccuring (check box) I would like the DateNext to be 1 year from DateComp if Reccuring is true and Date next to be blank if Reccuring is false. -- Aloha, Ron A.
From: Jeff Boyce on 6 Apr 2010 15:11 Ron I'll assume that you want to use the form to calculate the DateNext, and that you are not trying to store that calculated value in your underlying table. So, if the record is checked "Recurring", do you want to see ONLY 1 year after DateComp, or do you want to see the "anniversary" date (1 year and multiples thereof)? More info, please... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Ron A." <RonA(a)discussions.microsoft.com> wrote in message news:A2A88479-8D7A-4AF7-B53B-9C89A430F817(a)microsoft.com... >I need assistance on an expression that allows for the due date of >recurring > training to be one year from date completed. Details are as follows: > > I have a form named [frmTRNComplete] based on table named [tblTRNComplete] > > Form controls are: TRNID (combo box), DateComp (Text Box) , DateNext > (text > box), Reccuring (check box) > > I would like the DateNext to be 1 year from DateComp if Reccuring is true > and Date next to be blank if Reccuring is false. > -- > Aloha, > Ron A.
From: Ron A. on 6 Apr 2010 20:27 Actually, I do need the value to be stored in the underlying table. I am using the subform to populate the underlying table, I do not want to have type the NextDue date if access can calculate it for me, especially since our reoccuring training frequency is always 1 year. I would like the date to be 1 year from the DateComp if the recurring check box is true and blank otherwise. -- Aloha, Ron A. "Jeff Boyce" wrote: > Ron > > I'll assume that you want to use the form to calculate the DateNext, and > that you are not trying to store that calculated value in your underlying > table. > > So, if the record is checked "Recurring", do you want to see ONLY 1 year > after DateComp, or do you want to see the "anniversary" date (1 year and > multiples thereof)? > > More info, please... > > Regards > > Jeff Boyce > Microsoft Access MVP > > -- > Disclaimer: This author may have received products and services mentioned > in this post. Mention and/or description of a product or service herein > does not constitute endorsement thereof. > > Any code or pseudocode included in this post is offered "as is", with no > guarantee as to suitability. > > You can thank the FTC of the USA for making this disclaimer > possible/necessary. > > "Ron A." <RonA(a)discussions.microsoft.com> wrote in message > news:A2A88479-8D7A-4AF7-B53B-9C89A430F817(a)microsoft.com... > >I need assistance on an expression that allows for the due date of > >recurring > > training to be one year from date completed. Details are as follows: > > > > I have a form named [frmTRNComplete] based on table named [tblTRNComplete] > > > > Form controls are: TRNID (combo box), DateComp (Text Box) , DateNext > > (text > > box), Reccuring (check box) > > > > I would like the DateNext to be 1 year from DateComp if Reccuring is true > > and Date next to be blank if Reccuring is false. > > -- > > Aloha, > > Ron A. > > > . >
From: Allen Browne on 6 Apr 2010 22:13 As Jeff explained, you should not store the DateNext in your table, as this violates a basic rule of data normalization and will cause you grief. You could display the next date with a text box bound to an expression such as: =DMax("DateComp", "tblTRNComplete", "TRNID = " & Nz([TRNID],0) If you want to automatically assign the date one year from the last entry when entering a new record, you could use the AfterUpdate event procedure of the combo to assign it: Private Sub TRNID_AfterUpdate() Dim varLastDate As Variant If Me.NewRecord Then If Not IsNull(Me.TRNID) Then varLastDate = DMax("DateComp", "tblTRNComplete", "TRNID = " & Me.TRNID) If Not IsNull(varLastDate) Then Me.DateComp = DateAdd("yyyy", 1, varLastDate) End If End If End If End Sub If you need to calculate future dates on the fly as well, the techniques in this article may help: Recurring events at: http://allenbrowne.com/AppRecur.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ron A." <RonA(a)discussions.microsoft.com> wrote in message news:71B8B7C7-4705-4C0D-8E71-108A385D6894(a)microsoft.com... > Actually, I do need the value to be stored in the underlying table. I am > using the subform to populate the underlying table, I do not want to have > type the NextDue date if access can calculate it for me, especially since > our > reoccuring training frequency is always 1 year. I would like the date to > be 1 > year from the DateComp if the recurring check box is true and blank > otherwise. > -- > Aloha, > Ron A. > > > "Jeff Boyce" wrote: > >> Ron >> >> I'll assume that you want to use the form to calculate the DateNext, and >> that you are not trying to store that calculated value in your underlying >> table. >> >> So, if the record is checked "Recurring", do you want to see ONLY 1 year >> after DateComp, or do you want to see the "anniversary" date (1 year and >> multiples thereof)? >> >> More info, please... >> >> Regards >> >> Jeff Boyce >> Microsoft Access MVP >> >> -- >> Disclaimer: This author may have received products and services mentioned >> in this post. Mention and/or description of a product or service herein >> does not constitute endorsement thereof. >> >> Any code or pseudocode included in this post is offered "as is", with no >> guarantee as to suitability. >> >> You can thank the FTC of the USA for making this disclaimer >> possible/necessary. >> >> "Ron A." <RonA(a)discussions.microsoft.com> wrote in message >> news:A2A88479-8D7A-4AF7-B53B-9C89A430F817(a)microsoft.com... >> >I need assistance on an expression that allows for the due date of >> >recurring >> > training to be one year from date completed. Details are as follows: >> > >> > I have a form named [frmTRNComplete] based on table named >> > [tblTRNComplete] >> > >> > Form controls are: TRNID (combo box), DateComp (Text Box) , DateNext >> > (text >> > box), Reccuring (check box) >> > >> > I would like the DateNext to be 1 year from DateComp if Reccuring is >> > true >> > and Date next to be blank if Reccuring is false. >> > -- >> > Aloha, >> > Ron A. >> >> >> . >>
From: Ron A. on 7 Apr 2010 12:43
I am not sure I understand why the DateNext value would not be stored in the table. How then would I query employees that are overdue training if I don't store the value? Thanks for you patience. -- Aloha, Ron A. "Allen Browne" wrote: > As Jeff explained, you should not store the DateNext in your table, as this > violates a basic rule of data normalization and will cause you grief. > > You could display the next date with a text box bound to an expression such > as: > =DMax("DateComp", "tblTRNComplete", "TRNID = " & Nz([TRNID],0) > > If you want to automatically assign the date one year from the last entry > when entering a new record, you could use the AfterUpdate event procedure of > the combo to assign it: > Private Sub TRNID_AfterUpdate() > Dim varLastDate As Variant > If Me.NewRecord Then > If Not IsNull(Me.TRNID) Then > varLastDate = DMax("DateComp", "tblTRNComplete", "TRNID = " & > Me.TRNID) > If Not IsNull(varLastDate) Then > Me.DateComp = DateAdd("yyyy", 1, varLastDate) > End If > End If > End If > End Sub > > If you need to calculate future dates on the fly as well, the techniques in > this article may help: > Recurring events > at: > http://allenbrowne.com/AppRecur.html > > -- > Allen Browne - Microsoft MVP. Perth, Western Australia > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > > "Ron A." <RonA(a)discussions.microsoft.com> wrote in message > news:71B8B7C7-4705-4C0D-8E71-108A385D6894(a)microsoft.com... > > Actually, I do need the value to be stored in the underlying table. I am > > using the subform to populate the underlying table, I do not want to have > > type the NextDue date if access can calculate it for me, especially since > > our > > reoccuring training frequency is always 1 year. I would like the date to > > be 1 > > year from the DateComp if the recurring check box is true and blank > > otherwise. > > -- > > Aloha, > > Ron A. > > > > > > "Jeff Boyce" wrote: > > > >> Ron > >> > >> I'll assume that you want to use the form to calculate the DateNext, and > >> that you are not trying to store that calculated value in your underlying > >> table. > >> > >> So, if the record is checked "Recurring", do you want to see ONLY 1 year > >> after DateComp, or do you want to see the "anniversary" date (1 year and > >> multiples thereof)? > >> > >> More info, please... > >> > >> Regards > >> > >> Jeff Boyce > >> Microsoft Access MVP > >> > >> -- > >> Disclaimer: This author may have received products and services mentioned > >> in this post. Mention and/or description of a product or service herein > >> does not constitute endorsement thereof. > >> > >> Any code or pseudocode included in this post is offered "as is", with no > >> guarantee as to suitability. > >> > >> You can thank the FTC of the USA for making this disclaimer > >> possible/necessary. > >> > >> "Ron A." <RonA(a)discussions.microsoft.com> wrote in message > >> news:A2A88479-8D7A-4AF7-B53B-9C89A430F817(a)microsoft.com... > >> >I need assistance on an expression that allows for the due date of > >> >recurring > >> > training to be one year from date completed. Details are as follows: > >> > > >> > I have a form named [frmTRNComplete] based on table named > >> > [tblTRNComplete] > >> > > >> > Form controls are: TRNID (combo box), DateComp (Text Box) , DateNext > >> > (text > >> > box), Reccuring (check box) > >> > > >> > I would like the DateNext to be 1 year from DateComp if Reccuring is > >> > true > >> > and Date next to be blank if Reccuring is false. > >> > -- > >> > Aloha, > >> > Ron A. > >> > >> > >> . > >> > . > |