If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Due Date Calculated
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 a 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. |
#2
|
|||
|
|||
Due Date Calculated
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." wrote in message ... 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 a 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. |
#3
|
|||
|
|||
Due Date Calculated
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." wrote in message ... 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 a 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. . |
#4
|
|||
|
|||
Due Date Calculated
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." wrote in message ... 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." wrote in message ... 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 a 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. . |
#5
|
|||
|
|||
Due Date Calculated
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." wrote in message ... 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." wrote in message ... 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 a 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. . . |
#6
|
|||
|
|||
Due Date Calculated
On Wed, 7 Apr 2010 09:43:01 -0700, Ron A.
wrote: 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? By using a Query, and applying criteria to the calculated field *in the query*. It is NOT necessary to have a field stored in a table in order to display it, search it, sort by it or much of anything else. All of these operations can (and, in a case like this, should) be done on a Query. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|