Updating Calculated field depending on Current Date 
Author Message
 Updating Calculated field depending on Current Date

I have a field  called Enroll Date that I am using to generate the
calculated field called NA (needs assessment) Date.  The NA Date field is a
continuous cycle, meaning that every 6 months after the Enroll date, a needs
assessment has to be performed.  The coding works up until I have an Enroll
date that is 1 1/2 years older than the current date.  There's a problem
with my
Do Loop but I have not been able to correct it.  Any comments or suggestion
on correcting my code would really be appreciated.  Here's my coding:
----------------------------------------------------------------------------
Function NADate(EnrollDate As Variant) As String
Dim Update As Date
Dim NewDate As Date

If IsNull(EnrollDate) Then
    NADate = "No EnrollDate"
Else
    Update = DateAdd("m", 6, (EnrollDate))
    If (Update >= Date) Then
'Correctly displays NA date 6 months after current date
        NADate = Update
    Else
'This is where I am having a problem with the coding
'When the Update is < the Current date I want
'it to loop through until New date is > Current date
        Do
            NewDate = DateAdd("m", 6, (Update))
            If (NewDate < Date) Then
                NewDate = DateAdd("m", 6, NewDate)
            End If
            If (NewDate > Date) Then Exit Do
        Loop
           NADate = NewDate
    End If
End If
End Function
----------------------------------------------------------------------

Chuck Boulineau



Mon, 16 Aug 2004 22:07:58 GMT  
 Updating Calculated field depending on Current Date
Since you're using Update in your initial calculation of NewDate at the
beginning of the loop, you're resetting the value of NewDate to the same
thing each time you loop.  Set NewDate using Update before the loop starts,
like this:

         NewDate = DateAdd("m", 6, (Update))
         Do While NewDate < Date
                 NewDate = DateAdd("m", 6, NewDate)
         Loop
         NADate = NewDate

Should solve your problem.

RJ


Quote:
> I have a field  called Enroll Date that I am using to generate the
> calculated field called NA (needs assessment) Date.  The NA Date field is
a
> continuous cycle, meaning that every 6 months after the Enroll date, a
needs
> assessment has to be performed.  The coding works up until I have an
Enroll
> date that is 1 1/2 years older than the current date.  There's a problem
> with my
> Do Loop but I have not been able to correct it.  Any comments or
suggestion
> on correcting my code would really be appreciated.  Here's my coding:
> --------------------------------------------------------------------------
--
> Function NADate(EnrollDate As Variant) As String
> Dim Update As Date
> Dim NewDate As Date

> If IsNull(EnrollDate) Then
>     NADate = "No EnrollDate"
> Else
>     Update = DateAdd("m", 6, (EnrollDate))
>     If (Update >= Date) Then
> 'Correctly displays NA date 6 months after current date
>         NADate = Update
>     Else
> 'This is where I am having a problem with the coding
> 'When the Update is < the Current date I want
> 'it to loop through until New date is > Current date
>         Do
>             NewDate = DateAdd("m", 6, (Update))
>             If (NewDate < Date) Then
>                 NewDate = DateAdd("m", 6, NewDate)
>             End If
>             If (NewDate > Date) Then Exit Do
>         Loop
>            NADate = NewDate
>     End If
> End If
> End Function
> ----------------------------------------------------------------------

> Chuck Boulineau



Tue, 17 Aug 2004 07:45:36 GMT  
 Updating Calculated field depending on Current Date
Ray,

I really appreciate the help.  I need to work with loops more.  I knew there
was a problem with the loop but, just could not figure it out.  Hope you
have a great day.

Sincerely,
Chuck


Quote:
> Since you're using Update in your initial calculation of NewDate at the
> beginning of the loop, you're resetting the value of NewDate to the same
> thing each time you loop.  Set NewDate using Update before the loop
starts,
> like this:

>          NewDate = DateAdd("m", 6, (Update))
>          Do While NewDate < Date
>                  NewDate = DateAdd("m", 6, NewDate)
>          Loop
>          NADate = NewDate

> Should solve your problem.

> RJ



> > I have a field  called Enroll Date that I am using to generate the
> > calculated field called NA (needs assessment) Date.  The NA Date field
is
> a
> > continuous cycle, meaning that every 6 months after the Enroll date, a
> needs
> > assessment has to be performed.  The coding works up until I have an
> Enroll
> > date that is 1 1/2 years older than the current date.  There's a problem
> > with my
> > Do Loop but I have not been able to correct it.  Any comments or
> suggestion
> > on correcting my code would really be appreciated.  Here's my coding:

> --------------------------------------------------------------------------
> --
> > Function NADate(EnrollDate As Variant) As String
> > Dim Update As Date
> > Dim NewDate As Date

> > If IsNull(EnrollDate) Then
> >     NADate = "No EnrollDate"
> > Else
> >     Update = DateAdd("m", 6, (EnrollDate))
> >     If (Update >= Date) Then
> > 'Correctly displays NA date 6 months after current date
> >         NADate = Update
> >     Else
> > 'This is where I am having a problem with the coding
> > 'When the Update is < the Current date I want
> > 'it to loop through until New date is > Current date
> >         Do
> >             NewDate = DateAdd("m", 6, (Update))
> >             If (NewDate < Date) Then
> >                 NewDate = DateAdd("m", 6, NewDate)
> >             End If
> >             If (NewDate > Date) Then Exit Do
> >         Loop
> >            NADate = NewDate
> >     End If
> > End If
> > End Function
> > ----------------------------------------------------------------------

> > Chuck Boulineau



Tue, 17 Aug 2004 21:07:37 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Changing a Calculate field (Date) depending on Current date

2. When value changes, update other field with current date

3. How do you update a null date into an access table date field

4. Making a control invisible depending on calculated value in Access'97

5. Want to auto update date field based on adding check in another field

6. ADO Update of calculated field

7. calculated field and update

8. How to update calculated fields in SQL query?

9. Calculating date fields

10. Calculating Date Field

11. Need Help! calculating date fields

12. HELP me calculate a date field VERY Important!!!

 

 
Powered by phpBB® Forum Software