dminx13
asked on
Leap Year
Currently have fields that return this years date and then a year from today. Which we want to be the same date next year. In normal years this is fine, but next year is a leap year.
What can we do to alter the formula that we are currently using: DateAdd("yyyy", 1, .Fields("To")) to account for it. Or is there a better formaula to use? Resulting in 3/14/2010 to 3/13/2011
Thanks!
What can we do to alter the formula that we are currently using: DateAdd("yyyy", 1, .Fields("To")) to account for it. Or is there a better formaula to use? Resulting in 3/14/2010 to 3/13/2011
Thanks!
This might work for you:
DateSerial(Year(date)+1, Month(date) + 1, 0)
Flyster
DateSerial(Year(date)+1, Month(date) + 1, 0)
Flyster
In looking this over I don't get the problem
d = #2011-03-23#
? dateadd("yyyy",1,date)
2012-03-23
d = #2011-03-23#
? dateadd("yyyy",1,date)
2012-03-23
That should be:
d = #2011-03-23#
? dateadd("yyyy",1,d)
2012-03-23
d = #2011-03-23#
? dateadd("yyyy",1,d)
2012-03-23
Disregard my first post. That will give you the last day of the month a year from now. This one will give you the result you listed. If you enter 3/1/11 it will return 2/29/12:
NextYear: DateSerial(Year([YourDateF ield])+1,M onth([Your DateField] ),Day([You rDateField ])-1)
NextYear: DateSerial(Year([YourDateF
dminx13,
If this is true " we want to be the same date next year" then you what to use:
DateAdd("yyyy", 1, .Fields("To"))
It handles leap years
or
If this is true " we want to be the same date next year" then you what to use:
DateAdd("yyyy", 1, .Fields("To"))
It handles leap years
? DateAdd("yyyy", 1, #3/1/2011#)
3/1/2012
? DateAdd("yyyy", 1, #3/1/2012#)
3/1/2013
? DateAdd("yyyy", 1, #3/1/2013#)
3/1/2014
or
? DateAdd("yyyy", 1, #3/1/2011#)-1
2/29/2012
3/1/2012
? DateAdd("yyyy", 1, #3/1/2012#)-1
2/28/2013
3/1/2013
? DateAdd("yyyy", 1, #3/1/2013#)-1
2/28/2014
3/1/2014
It is not clear what you are after, as DateAdd handles leap year correctly.
If you wish to deduct one day - to obtain Mar. 13th from Mar. 14th the previous year - deduct one day first, then add one year:
datNextYear = DateAdd("yyyy", 1, DateAdd("d", -1, .Fields("To")))
/gustav
If you wish to deduct one day - to obtain Mar. 13th from Mar. 14th the previous year - deduct one day first, then add one year:
datNextYear = DateAdd("yyyy", 1, DateAdd("d", -1, .Fields("To")))
/gustav
Why not use DateDiff - calculate the days between this date and next date, and then use the answer to figure out if leap year or not. It's going to be 365 or 366. Let the computer figure it out.
Scott C
Scott C
But why? Neither 2010 nor 2011 are leap years so something else is going on ...
/gustav
/gustav
Leap years are evenly divided by 4 (if I remember correctly from the 'old days')
Scott C
Scott C
Yes, thus 2008 and 2012 but not 2009-2011.
/gustav
/gustav
Leap years are evenly divided by 4 is close but not the complete.
1900 divides evenly by 4 but is not a leap year. Why? If the year ends in 00 it must also be evenly divisible by 400.
Algorithm
Pseudocode to determine whether a year is a leap year or not in either the Gregorian calendar since 1582 or in the proleptic Gregorian calendar before 1582:
1900 divides evenly by 4 but is not a leap year. Why? If the year ends in 00 it must also be evenly divisible by 400.
Algorithm
Pseudocode to determine whether a year is a leap year or not in either the Gregorian calendar since 1582 or in the proleptic Gregorian calendar before 1582:
if year modulo 400 is 0
then is_leap_year
else if year modulo 100 is 0
then not_leap_year
else if year modulo 4 is 0
then is_leap_year
else
not_leap_year
Just let DateSerial carry the hard work:
Public Function IsLeapYear( _
ByVal intYear As Integer) _
As Boolean
IsLeapYear = Day(DateSerial(intYear, 2, 29)) = 29
End Function
/gustav
ASKER
Sorry I think I was a little unclear.
We are using DateAdd("yyyy", 1, .Fields("To")) and when we are going from this year to next year we are getting the incorrect date. This year is 3/14/2011 next year with the above formula is giving us 3/13/2012, but it SHOULD BE 3/14/2011. So the formula is giving us the incorrect return that we want.
We are using DateAdd("yyyy", 1, .Fields("To")) and when we are going from this year to next year we are getting the incorrect date. This year is 3/14/2011 next year with the above formula is giving us 3/13/2012, but it SHOULD BE 3/14/2011. So the formula is giving us the incorrect return that we want.
YourDate = dateadd("d",datediff("d", YourFirstDate, YourSecondDate), YourFirstDate)
The leap year will be included in the datediff function - computer knows what years are leap.
Scott C
The leap year will be included in the datediff function - computer knows what years are leap.
Scott C
Further.. in my examples (last comment), YourSecondDate will be the same mm/dd as your first date - but the next year.
Scott C
Scott C
But that is not so.
datNextYear = DateAdd("yyyy", 1, #3/14/2011#)
does return 2012-03-14.
Leap year or not, there is no way it could return 2012-03-13.
So what does your .Fields("To")) really hold?
/gustav
datNextYear = DateAdd("yyyy", 1, #3/14/2011#)
does return 2012-03-14.
Leap year or not, there is no way it could return 2012-03-13.
So what does your .Fields("To")) really hold?
/gustav
dminx13: re your post at http#a35207664 go to the immediate pane and type:
? dateadd("yyyy",1,#2011-03- 14#)
2012-03-14
Are you saying you do not get this date for next year?
? dateadd("yyyy",1,#2011-03-
2012-03-14
Are you saying you do not get this date for next year?
ASKER
GRayL:
Correct. We have that formula in Access 2003 and it is returning 3/13/2012 not 3/14/2012 which is what we want it to say.
CactusData - the .Fields("To")) is the date this year. It currently is holding 3/14/2011. So we want next years to be 3/14/2012 yet it isn't. It is showing 3/13/2012.
Correct. We have that formula in Access 2003 and it is returning 3/13/2012 not 3/14/2012 which is what we want it to say.
CactusData - the .Fields("To")) is the date this year. It currently is holding 3/14/2011. So we want next years to be 3/14/2012 yet it isn't. It is showing 3/13/2012.
Again, that is not possible. Thus, something else is going on.
First thing to check is the real content of .Fields("To")).
/gustav
First thing to check is the real content of .Fields("To")).
/gustav
Did you go to the Immediate Pane and make the test? If so, does this happen on all your machines or just one. If the latter, me thinks you should re-install Access.
ASKER
Yes, yes it is happening............ It is consistant. Examples:
To = 3/26/2011 NextReview = 3/25/2012
To = 3/22/2011 NextReview = 3/21/2012
To = 3/19/2011 NextReview = 3/18/2012
To = 3/11/2011 NextReview = 3/10/2012
To = 3/6/2011 NextReview = 3/5/2012
Here is the whole section of code:
Private Sub DefaultLastReview()
' Procedure is used to locate the last review record in the
' recordset information and load default information based on the prior review
Dim dtmLastEffective As Date
Dim dtmTo As Date
Dim dtmFrom As Date
Dim dtmNext As Date
Dim strType As String
With Review
If .EOF And .BOF Then
' no prior records
Else
.MoveFirst
Do
If .RecordCount = 0 Then
dtmLastEffective = .Fields("EffectiveDate")
Else
If .Fields("EffectiveDate") > dtmLastEffective Then
dtmLastEffective = .Fields("EffectiveDate")
End If
End If
.MoveNext
Loop Until .EOF
End If
' Locate this record and use to default in the information
If .EOF And .BOF Then
' no prior data
Else
.MoveFirst
Do
If .Fields("EffectiveDate") = dtmLastEffective Then Exit Do
.MoveNext
Loop Until .EOF
' What type of review was the last review
strType = Me.cboReviewType
Select Case strType
Case "Annual"
' Move everything up one year
If IsNull(.Fields("To")) = False Then
dtmTo = DateAdd("yyyy", 1, .Fields("To"))
Else
dtmTo = Date
End If
If IsNull(.Fields("From")) = False Then
dtmFrom = DateAdd("yyyy", 1, .Fields("From"))
Else
dtmFrom = DateAdd("YYYY", -1, Date)
End If
If IsNull(.Fields("NextReview ")) = False Then
dtmNext = DateAdd("yyyy", 1, .Fields("NextReview"))
Else
dtmNext = DateAdd("yyyy", 1, Date)
End If
Case "Intro"
If IsNull(.Fields("From")) = False Then
dtmTo = DateAdd("yyyy", 1, .Fields("From"))
Else
dtmTo = Date
End If
If IsNull(.Fields("From")) = False Then
dtmFrom = .Fields("From")
Else
dtmFrom = DateAdd("yyyy", -1, Date)
End If
If IsNull(.Fields("From")) = False Then
dtmNext = DateAdd("yyyy", 1, .Fields("From"))
Else
dtmNext = DateAdd("yyyy", 1, Date)
End If
Case "Extend"
If IsNull(.Fields("FROM")) = False Then
dtmTo = DateAdd("yyyy", 1, .Fields("From"))
Else
dtmTo = Date
End If
If IsNull(.Fields("From")) = False Then
dtmFrom = .Fields("From")
Else
dtmFrom = Date
End If
If IsNull(.Fields("From")) = False Then
dtmNext = DateAdd("yyyy", 1, .Fields("From"))
Else
dtmNext = DateAdd("yyyy", 1, Date)
End If
Me.cboRating = "BP"
Case Else
' MRD review Change - default the same information as the last review
dtmTo = .Fields("To")
dtmFrom = .Fields("From")
dtmNext = .Fields("NextReview")
Me.cboRating.Value = .Fields("Rating")
End Select
End If
End With
' Load the default values
If dtmTo <> #12:00:00 AM# Then
Me.txtTo.Value = dtmTo
Me.txtFrom.Value = dtmFrom
Me.txtNextReview.Value = dtmNext
End If
End Sub
To = 3/26/2011 NextReview = 3/25/2012
To = 3/22/2011 NextReview = 3/21/2012
To = 3/19/2011 NextReview = 3/18/2012
To = 3/11/2011 NextReview = 3/10/2012
To = 3/6/2011 NextReview = 3/5/2012
Here is the whole section of code:
Private Sub DefaultLastReview()
' Procedure is used to locate the last review record in the
' recordset information and load default information based on the prior review
Dim dtmLastEffective As Date
Dim dtmTo As Date
Dim dtmFrom As Date
Dim dtmNext As Date
Dim strType As String
With Review
If .EOF And .BOF Then
' no prior records
Else
.MoveFirst
Do
If .RecordCount = 0 Then
dtmLastEffective = .Fields("EffectiveDate")
Else
If .Fields("EffectiveDate") > dtmLastEffective Then
dtmLastEffective = .Fields("EffectiveDate")
End If
End If
.MoveNext
Loop Until .EOF
End If
' Locate this record and use to default in the information
If .EOF And .BOF Then
' no prior data
Else
.MoveFirst
Do
If .Fields("EffectiveDate") = dtmLastEffective Then Exit Do
.MoveNext
Loop Until .EOF
' What type of review was the last review
strType = Me.cboReviewType
Select Case strType
Case "Annual"
' Move everything up one year
If IsNull(.Fields("To")) = False Then
dtmTo = DateAdd("yyyy", 1, .Fields("To"))
Else
dtmTo = Date
End If
If IsNull(.Fields("From")) = False Then
dtmFrom = DateAdd("yyyy", 1, .Fields("From"))
Else
dtmFrom = DateAdd("YYYY", -1, Date)
End If
If IsNull(.Fields("NextReview
dtmNext = DateAdd("yyyy", 1, .Fields("NextReview"))
Else
dtmNext = DateAdd("yyyy", 1, Date)
End If
Case "Intro"
If IsNull(.Fields("From")) = False Then
dtmTo = DateAdd("yyyy", 1, .Fields("From"))
Else
dtmTo = Date
End If
If IsNull(.Fields("From")) = False Then
dtmFrom = .Fields("From")
Else
dtmFrom = DateAdd("yyyy", -1, Date)
End If
If IsNull(.Fields("From")) = False Then
dtmNext = DateAdd("yyyy", 1, .Fields("From"))
Else
dtmNext = DateAdd("yyyy", 1, Date)
End If
Case "Extend"
If IsNull(.Fields("FROM")) = False Then
dtmTo = DateAdd("yyyy", 1, .Fields("From"))
Else
dtmTo = Date
End If
If IsNull(.Fields("From")) = False Then
dtmFrom = .Fields("From")
Else
dtmFrom = Date
End If
If IsNull(.Fields("From")) = False Then
dtmNext = DateAdd("yyyy", 1, .Fields("From"))
Else
dtmNext = DateAdd("yyyy", 1, Date)
End If
Me.cboRating = "BP"
Case Else
' MRD review Change - default the same information as the last review
dtmTo = .Fields("To")
dtmFrom = .Fields("From")
dtmNext = .Fields("NextReview")
Me.cboRating.Value = .Fields("Rating")
End Select
End If
End With
' Load the default values
If dtmTo <> #12:00:00 AM# Then
Me.txtTo.Value = dtmTo
Me.txtFrom.Value = dtmFrom
Me.txtNextReview.Value = dtmNext
End If
End Sub
Very weird.
Try this debug:
If IsNull(.Fields("To")) = False Then
dtmTo = DateAdd("yyyy", 1, .Fields("To"))
Debug.Print .Fields("To")), dtmTo
Else
dtmTo = Date
End If
and tell us what it prints.
/gustav
Try this debug:
If IsNull(.Fields("To")) = False Then
dtmTo = DateAdd("yyyy", 1, .Fields("To"))
Debug.Print .Fields("To")), dtmTo
Else
dtmTo = Date
End If
and tell us what it prints.
/gustav
I repeat, did you go to the Immediate Pane and make the simlple test I showed you at http:#a35208736 ??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dminx13,
Is the issue only with the the cboReviewType of Annual"?
Only the case for Annual" uses the "To" date.
Is the issue only with the the cboReviewType of Annual"?
Only the case for Annual" uses the "To" date.
Case "Annual"
' Move everything up one year
If IsNull(.Fields("To")) = False Then
dtmTo = DateAdd("yyyy", 1, .Fields("To"))
Else
dtmTo = Date
End If
If IsNull(.Fields("From")) = False Then
dtmFrom = DateAdd("yyyy", 1, .Fields("From"))
Else
dtmFrom = DateAdd("YYYY", -1, Date)
End If
If IsNull(.Fields("NextReview")) = False Then
dtmNext = DateAdd("yyyy", 1, .Fields("NextReview"))
Else
dtmNext = DateAdd("yyyy", 1, Date)
End If
ASKER
Busy day, much to do at work, will check all these in the am tomorrow and let you know what I find out.
ASKER
OK, so after going through all of this. We found that there was another piece of code in another form that had not been set to DateAdd("yyyy", 1, .Fields("To")) is was set to "d", 365. Which would give the incorrect date. We have updated that code to be the yyyy and hope that fixes the problem.
Thanks to all that assisted.
Thanks to all that assisted.
As I said at http:#a35208971
OK, that explains.
Don't forget to kill your coprogrammer who counts a year like that.
/gustav
Don't forget to kill your coprogrammer who counts a year like that.
/gustav
ASKER
It was the code, it was just not that exact piece of code. There was another piece of coding in a completely different form that was being used.
d = #2011-03-14#
? dateadd("d",365,d)
2012-03-13