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.

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.

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.


.

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.


.

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.


.

.

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]