Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Leap Year

Posted on 2011-03-23
31
Medium Priority
?
961 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:dminx13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 6
  • +3
31 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 35203657
Do you mean like this:

d = #2011-03-14#
? dateadd("d",365,d)
2012-03-13
0
 
LVL 22

Expert Comment

by:Flyster
ID: 35204072
This might work for you:

DateSerial(Year(date)+1, Month(date) + 1, 0)

Flyster
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35204195
In looking this over I don't get the problem

d =  #2011-03-23#
? dateadd("yyyy",1,date)
2012-03-23
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 44

Expert Comment

by:GRayL
ID: 35204198
That should be:

d =  #2011-03-23#
? dateadd("yyyy",1,d)
2012-03-23
0
 
LVL 22

Expert Comment

by:Flyster
ID: 35204398
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([YourDateField])+1,Month([YourDateField]),Day([YourDateField])-1)
0
 
LVL 21
ID: 35205251
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

? 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 

Open in new window


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 

Open in new window

0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35205851
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
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 35205899
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

0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35205917
But why? Neither 2010 nor 2011 are leap years so something else is going on ...

/gustav
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 35205937
Leap years are evenly divided by 4 (if I remember correctly from the 'old days')
Scott C
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35206008
Yes, thus 2008 and 2012 but not 2009-2011.

/gustav
0
 
LVL 21
ID: 35206740
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:

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

Open in new window


0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35207046
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

Open in new window

/gustav
0
 

Author Comment

by:dminx13
ID: 35207664
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.

0
 
LVL 20

Expert Comment

by:clarkscott
ID: 35207813
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
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 35208023
Further.. in my examples (last comment), YourSecondDate will be the same mm/dd as your first date - but the next year.

Scott C
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35208032
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
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35208736
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?
0
 

Author Comment

by:dminx13
ID: 35208788
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.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35208811
Again, that is not possible. Thus, something else is going on.
First thing to check is the real content of .Fields("To")).

/gustav
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35208820
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.
0
 

Author Comment

by:dminx13
ID: 35208888
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
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35208954
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
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35208958
I repeat, did you go to the Immediate Pane and make the simlple test I showed you at http:#a35208736  ??
0
 
LVL 44

Accepted Solution

by:
GRayL earned 750 total points
ID: 35208971
I could be that your code is the culprit.
0
 
LVL 21
ID: 35211175
dminx13,


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

Open in new window


0
 

Author Comment

by:dminx13
ID: 35211956
Busy day, much to do at work, will check all these in the am tomorrow and let you know what I find out.
0
 

Author Comment

by:dminx13
ID: 35233509
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.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35233544
As I said at http:#a35208971
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 35233605
OK, that explains.
Don't forget to kill your coprogrammer who counts a year like that.

/gustav
0
 

Author Closing Comment

by:dminx13
ID: 35233645
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.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question