From: Ron A. on
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
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
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
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
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.
> >>
> >>
> >> .
> >>
> .
>