Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    May 2008
    Location
    Manchester, England
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts

    ACCESS: Calculating the difference between two dates

    Hi there,

    this one should be pretty simple for alot of you but i've been tearing my hair out for a good hour now trying to find the solution online. Basically my problem is this:

    I have two fields in my table, startdate and enddate. I need to calculate the number of years between the two dates. I have done this using YearsService: DateDiff("yyyy",[StartDate],[EndDate]).

    All working fine, however if there is no EndDate present then no value is displayed. How can i add an extra part to this expression that says, if there is no end date present, use todays date.

    Regards

    Tebbott

  • #2
    New Coder
    Join Date
    May 2008
    Location
    Manchester, England
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Think this is probably the wrong place to post this question in retrospect. I was under the impression that microsoft access uses mySQL (maybe it does, im still unsure) but after reading other posts here it would seem that i am mis-informed. However, i will leave the post up until my suspissions are verified. Never know, the answer might be so obvious to coders that somebody could help me anyway.

    Tebb

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    microsoft access and mysql are two entire different database systems.

    you may still be able to use COALESCE though. COALESCE says to check for a value in the column and if the value is NULL (note that isn't an empty string like ' ', or '' or a space) then to use something else indicated.

    this is how I'd write it in mysql, you can probably adapt it to access:

    Code:
    SELECT
      DATEDIFF(COALESCE(enddate,current_date()), startdate)
    FROM
      yourtablenamehere
    so essentially if there is a null in the enddate field then the current_date is calculated instead.


  •  

    Posting Permissions

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