1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

calculate date in proper format

Discussion in 'Business Applications' started by wdisneymom, May 18, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. wdisneymom

    wdisneymom Thread Starter

    Joined:
    Jan 21, 2001
    Messages:
    283
    I'm using access 2003 and have a query based on a table
    I have the kid's dob as a field and I want to calculate today's age...I've done that with today()-[dob]
    But it comes up in a very large number and when I divide manually by 365 it's correct
    When I do it in the program it comes up in time format
    Any suggestions?
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    format the field as a number
     
  3. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    I'm not quite sure what you meant by that last line, "When I do it in the program it comes up in time format." There are a couple links in my signature that explain Date/Time data types in Access. There is a function called DATEDIFF that will probably serve you better than TODAY() - [dob]. You may find an overview at the following site:

    HTH

    chris.

    [edit]
    If you're just after the difference in years, you might try this:

    Code:
    DATEDIFF("yyyy", [dob], TODAY())
    
    [/edit]
     
  4. deej

    deej

    Joined:
    Jun 11, 2003
    Messages:
    152
    You just need to a bit careful with that DateDiff function. If you specify difference in years ('yyyy') it will only work on the year part of each date, e.g. if today is 20 May 2007 and [dob] is 20 May 2000 it will return an age of 7 years (correct), but if today is 20 May 2007 and [dob] is any date between 21 May 2000 and 31 Dec 2000 it will still return an age of 7 years (incorrect of course).

    I have a VBA function I can let you have which you can use in the query (it also takes account of a birth date of 29th February in a leap year) but I need to know (i) is the data type for your [dob] field 'date' ('date/time') or is it a text field? and (ii) what format do you have for the date in the [dob] field?

    Deej
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/574773

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice