Results 1 to 9 of 9
  1. #1
    SandyM
    Guest

    Now Date in Expression

    I have a table with an Anniversary Date field and a Tenure field. The Anniversary Date is formatted as a date and Tenure as a number. Whenever the Anniversary Date and today's date are the same, I would like the Tenure field to add one to its number (it reflects number of years). Would I use an Update Query for inserting the expression, or a macro? Please advise where to make this happen and how to write the expression. Thanks so much...this forum is a tremendous time-saver!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Now Date in Expression

    Why do you have the tenure in the table at all? If you have the anniversary date, then you should always be able to calculate the tenure when it is needed.
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Now Date in Expression

    Sandy: I agree with Legare. Just use the DateDiff function =DateDiff("yyyy",[fldStartDate],Now()) to return the number of years wherever you need it. [img]/w3timages/icons/smile.gif[/img]

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Now Date in Expression

    <hr>NOTE: Sandy sent me a sample database with a query using my example to illustrate an error in my logic. Quite rightly, the example I gave will only subtract the years giving incorrect results<hr>
    The proper function would be: Tenure: DateDiff("yyyy",[StartDate],Now())+Int(Format(Now(),"mmdd")<Format([StartDate],"mmdd"))
    Sorry for the confusion [img]/w3timages/icons/hairy.gif[/img] Alternatively, this is a funtion which will return the same:

    Function Tenure(StartDate, DateToday) As Integer
    ' Returns the Tenure in years between 2 dates
    ' Doesn't handle negative date ranges i.e. StartDate > DateToday

    If Month(DateToday) < Month(StartDate) Or (Month(DateToday) = _
    Month(StartDate) And Day(DateToday) < Day(StartDate)) Then
    Tenure = Year(DateToday) - Year(StartDate) - 1
    Else
    Tenure = Year(DateToday) - Year(StartDate)
    End If
    End Function

    Credit for the above goes to
    <A target="_blank" HREF=http://www.mvps.org/access/index.html>The Access Web</A>, a great source of information.

  5. #5
    SandyM
    Guest

    Re: Now Date in Expression

    I tried the formula that Brian last posted and still no luck. The Years with '00 or '01 calculate to 100+ years rather than 1+. (I no longer have field "tenure" in table, but am using it strictly in the query.)

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Now Date in Expression

    Hi Sandy: What do you have set in your Control Panel|Regional Settings|Dates, e.g.
    Attached Images Attached Images

  7. #7
    SandyM
    Guest

    Re: Now Date in Expression

    My regional settings are the same as in your illustration; 1930 - 2029, Gregorian Calendar.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Now Date in Expression

    It sounds like those 00 and 01 dates are 1900 and 1901. Try displaying the date in those fields with a format that displays a four digit year and see what is in the field. I remember from my Y2K testing that older versions of Access default all 2 digit years to the 20th century (19xx). I don't remember wheich versions do it which way.
    Legare Coleman

  9. #9
    SandyM
    Guest

    Re: Now Date in Expression

    You're right. Yesterday I finally tried putting a format of mm-dd-yyyy in my table and now it is working correctly. Now it seems obvious and don't know why I didn't see it earlier. Guess everything looks easy once we figure it out. Thanks for your post!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •