Increment Access Date Field

shieldsco
shieldsco used Ask the Experts™
on
I have two  date fields on an Access form.  I want to increment the next proficiency field each year based on the date of grade field.                        
Date of Grade            Next Proficiency      
2/25/2000                  2/25/2013      
5/31/1998                  5/31/2012      
3/18/2012                  3/18/2013                               
In Addition if the date of grade field changes then I want the next proficiency field to change accordingly.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Basically you don't need the field [Next Proficiency] as far as I see.
Using the day and month field combined with the systemdate you can construct the [Next Proficiency] like:

select [Date of Grade], 
'is the current year with month and day after the current date, then use the current year, else increment the year:
IIF dateserial(Year(Date()),Month([Date of Grade]),Day([Date of Grade])) > Date() 
then dateserial(Year(Date()),Month([Date of Grade]),Day([Date of Grade])) 
else dateserial(Year(Date())+1,Month([Date of Grade]),Day([Date of Grade])) as  [Next Proficiency]
from tblYours

Open in new window


This will "drop" the need for storing and updating the field.

Author

Commented:
I need the proficiency field

Author

Commented:
I need to store the proficiency date in the table

Author

Commented:
Does anyone else have any ideals
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
How are you deciding the number of years to increase?

In your examples the next date is:
13, 14, then 1 year from the Date of grade...

Author

Commented:
No - in the original post there are 3 individual records.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<I need to store the proficiency date in the table>
Based on what database theory?
The "proficiency date" is a "calculated" value (Date of grade + some increment)

Standard database theory hold that in most cases you would not need to "Store" these values.
Instead you would use a query to calculate the next date, then use this query, like you would use a table.

In other words with a query, you can use the Calculated value just like a stored value.

For example, a query like this will add 5 years to the grade date:
SELECT RecordID, StudentID, ClassID, GradeDate, DateAdd("yyyy",5,[Gradedate]) AS NextProficiencyDate
FROM tblStudentClassGrades;

JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<No - in the original post there are 3 individual records.>
I know, but my question still remains...
How are you deciding how many years to increment the Grade date...?
In increment is different for all three records...

Author

Commented:
based on day and month of the current or subsequent year - for example if my date of grade is 2/15/2001 then the next proficiency date would be 2/15/2013 since 2/15/2012 has already passed.  I need to write the proficiency date to the table not a query.

Author

Commented:
I need vb code to write to the table
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
This is getting more confusing
<for example if my date of grade is 2/15/2001 then the next proficiency date would be 2/15/2013 since 2/15/2012 has already passed.>
What relevance does the 2/15/2012  have here?

One more time...
How are you deciding on the Next Date?


<I need vb code to write to the table>
Can we work on one issue at a time?
Again I see no need to store the next date in a table.
And you have not explained your system well enough for me to understand why you feel you need to store the calculated date...

Author

Commented:
it's pretty simple - if my date of grade is 1/2/1988 then my next proficiency would be 1/2/2013 because 1/2/2012 has aready past so naturally my next proficiency could not be in the past. Hint - look at the month and day of the date of grade and if the current month and day is > date of grade month and day then add 1 year to thecurrent year if not > it's the current year since current month and day is less then the date of grade month and day - take another look at the 3 examples that I provided.

Author

Commented:
reports have been written based on the table so therfore I need the next proficiency in the table - ok

Author

Commented:
there is one exception - if the date of grade is in the current year then the next proficiency date will always be in the subsuqent year - sorry for that oversight

Commented:
Dear shieldsco, the fact that a field is needed doesn't require that it's stored in a table.
When you can deduct the fieldvalue, than you can create a query with the field based on the available data.
So TableA with [Date of Grade]
and ViewA with the additional field:
select [Date of Grade], 
'is the current year with month and day after the current date, then use the current year, else increment the year:
IIF dateserial(Year(Date()),Month([Date of Grade]),Day([Date of Grade])) > Date() 
then dateserial(Year(Date()),Month([Date of Grade]),Day([Date of Grade])) 
else dateserial(Year(Date())+1,Month([Date of Grade]),Day([Date of Grade])) as  [Next Proficiency]
from tblYours

Open in new window

So your reports can work with the ViewA instead of the TableA.

To add the last requirement the query would look like:
select [Date of Grade], 
'is the current year with month and day after the current date, then use the current year, else increment the year:
IIF Year([Date of Grade])=Year(Date()) ,
 dateserial(Year(Date())+1,Month([Date of Grade]),Day([Date of Grade])) ,
IIF dateserial(Year(Date()),Month([Date of Grade]),Day([Date of Grade])) > Date() 
then dateserial(Year(Date()),Month([Date of Grade]),Day([Date of Grade])) 
else dateserial(Year(Date())+1,Month([Date of Grade]),Day([Date of Grade]))) as   dateserial(Year(Date())+1,Month([Date of Grade]),Day([Date of Grade])) from tblYours

Open in new window


So still no technical need to store the field in a table.

Using such a query will make sure you always have the correct Proficiency date, as when yours update hasn't been run for some reason, the reports will work on the wrong date...
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This is question about finding the next annual day.
You do that by finding the age in years, then add this + 1 to the original date.

Thus, using the function below which calculates correctly for leap years, for the ControlSource of Me![Next Proficiency] use this expression:

=DateAdd("yyyy",AgeSimple([Date of Grade])+1,[Date of Grade])
Public Function AgeSimple( _
  ByVal datDateOfBirth As Date) _
  As Integer

' Returns the difference in full years from datDateOfBirth to current date.
'
' Calculates correctly for:
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
' After an idea of Markus G. Fischer.
'
' 2007-06-26. Cactus Data ApS, CPH.

  Dim datToday  As Date
  Dim intAge    As Integer
  Dim intYears  As Integer
    
  datToday = Date
  ' Find difference in calendar years.
  intYears = DateDiff("yyyy", datDateOfBirth, datToday)
  If intYears > 0 Then
    ' Decrease by 1 if current date is earlier than birthday of current year
    ' using DateDiff to ignore a time portion of datDateOfBirth.
    intAge = intYears - Abs(DateDiff("d", datToday, DateAdd("yyyy", intYears, datDateOfBirth)) > 0)
  End If
  
  AgeSimple = intAge
  
End Function

Open in new window

/gustav

Author

Commented:
gustav - excellent job -- I guest the other guys did not really understand. Thanks so much

Author

Commented:
excellent job - thanks so much
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

And yes, Jeff can be a little dense. But hey, I'm at UTC +01.00 and just had my morning coffee!

/gustav

Commented:
Guess you (and cactus_data?) need to read an article on data normalisation.
Your field is redundant and you'll find out the consequences when you forget to run your update query one day.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Not needed.

> In Addition if the date of grade field changes then I want
> the next proficiency field to change accordingly.

That was what I answered. Nothing about normalization here.
Even then, the questioneer may have his/her reasons we don't know of.

/gustav
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
gustav

<And yes, Jeff can be a little dense.>
Just to be sure...
Was this meant to be an insult to me?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
shieldsco,

<I guest the other guys did not really understand.>
Or just perhaps the original question was not clear.
To be fair, certain experts can "Figure out" what is really needed though...

I am glad you got the answer you were seeking...

;-)

JeffCoachman
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
> Was this meant to be an insult to me?

Ha ha, (European) humour only.

/gustav

Commented:
Sad to see that even an expert here isn't aware of the danger of redundant data and fails to warn the auther. I rest my case.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
cactus_data,
<Ha ha, (European) humour only.>
Odd, ...I know people in Europe and none of them say that calling someone "dense" is considered "Humorous"
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
gg: Relax please.
Mike: It's of the subclass of humour called irony.

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial