Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    whitelexi is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    20

    Exclamation Automatic Age Calculation in Access using DateDiff

    Hello,



    I'm working on Windows Vista Business (32bit) and MS Access 2010.
    I'm currently working on a form, and on this form i have many fields. 2 of these fields are DateOfBirth and Age. The DateOfBirth field comes just before the Age field. I'm trying to get the Age field automatically populated as soon as a person fills out the DateOfBirth field. I have tried using the following code in the [Default Value] and it hasn't worked:

    =DateDiff("yyyy",[DateOfBirth],Date())-IIf(Format([DateOfBirth],"mmdd")>Format(Date(),"mmdd"),1,0)


    I would appreciate any assistance i get from you all.

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I hope this helps:
    I created a Table with FName, LName & DateOfBirth fields.
    Then I created a Query and pulled in all three fields and added a field to the query like this:
    Code:
    Age: DateDiff("yyyy",[DateOfBirth],Date())-IIf(Format([DateOfBirth],"mmdd")>Format(Date(),"mmdd"),1,0)
    . . . using almost the exact syntax that you posted.
    Then I created a Form based on the query. It works.

  3. #3
    mercapto is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Spain
    Posts
    91
    I think it's easier...


    Age: Fix((Now()-[DateOfBirth])/365.25)

  4. #4
    MsAdams is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2012
    Posts
    8
    I have used the following to calculate age in Access:

    =DateDiff([yourfield],Date(), "Y")

    You will more than likely need to do what the others have said and create a query with a calculated field.

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    There are several ways of calculating accurate ages, including the OP's! The problem is that the OP had the formula in the Default Value of a Control! The Default Value is assigned at the instance that a New Record is originally created, and at that point there is no data in the DateOfBirth Field. If this is done at the Form-level, the formula needs to be in the Control Source of the Textbox, but I really prefer using a Calculated Field in a Query, as suggested by Robeen.

    And I'm sorry, MsAdams, but you've never calculated an age with this formula!

    =DateDiff([yourfield],Date(), "Y")

    The DateDiff is completely malformed. I suspect that you meant

    =DateDiff('y',[yourfield],Date())

    but even that will not always give you an accurate age, because, unlike the OP's formula, it does not take into account whether the person has already celebrated their birthday for the current year! If the birthday hasn't been feted for the current year, the age returned will be one more than the actual age!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not to mention that the AGE (a calculated value) shouldn't be stored, because after their next birthday, the AGE field value is wrong. I suppose that you could run an update query EVERY DAY to update the age..... what a pain.

    Since the AGE can be calculated, calculate it in a query and bind a control to the query column. There you go - AGE is never wrong!!

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by ssanfu View Post

    ...I suppose that you could run an update query EVERY DAY to update the age
    Or, simply rerun the calculation in the OnCurrent event, assuming a Single View Form! Of course, I've put this forth so many times, in the past, that if I had patented it, I'd be in Aruba now, on the beach, instead of sitting here, in chilly, damp Virginia, remembering better days!

    Have a great weekend, ssanfu!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'd be in Aruba now, on the beach, instead of sitting here, in chilly, damp Virginia, remembering better days!
    But at least you are not looking at 7 months of snow 4 feet deep (in AK)!!!

    Virginia...... yesssssss

  9. #9
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by ssanfu View Post
    But at least you are not looking at 7 months of snow 4 feet deep
    No, that would be my newly married daughter, in Boston!
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I agree, don't save age, calculate when needed.

    Steve, you exaggerate for Anchorage (wait that is good - scare away the cheechakos) but in Whittier and Valdez is another matter - 4 feet is nothing!
    Last edited by June7; 10-12-2012 at 07:54 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Well, maybe not 7 months and 4 feet.... 3 - 4 months and 4 feet (upper hillside).

    Was in Valdez on a tour when the snow was 25 - 30 feet. Like driving in canyons...

  12. #12
    whitelexi is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    20
    Quote Originally Posted by Robeen View Post
    I hope this helps:
    I created a Table with FName, LName & DateOfBirth fields.
    Then I created a Query and pulled in all three fields and added a field to the query like this:
    Code:
    Age: DateDiff("yyyy",[DateOfBirth],Date())-IIf(Format([DateOfBirth],"mmdd")>Format(Date(),"mmdd"),1,0)
    . . . using almost the exact syntax that you posted.
    Then I created a Form based on the query. It works.
    I deleted the Age field in my table just to eradicate storage of age - @Missinglinq
    Then i removed the Age field from my form as well.
    Created the query as @Robeen highlighted, tested the query and it worked ok.
    Then i recreated the Age field on my form and tried binding it to the Age field in the query i created earlier through Control Source=>Expression builder=>=[QryAge_Calculation]![Age]
    And it is not working. Am i doing something the wrong way? Please help me, you guys have been very helpful and i have already learnt a few nice things.

  13. #13
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    You could go to the 'Add Existing Fields' button [towards the top right of your screen - just to the left of the Property Sheet button] and then drag & drop the new field onto your Form. Did you already try that?
    BUT . . . you should be able to do it by selecting the TextBox control - Property Sheet -> Data Tab -> click the down-arrow in the Control Source -> select the field you want to bind the control to.
    I don't understand why you used the Expression Builder to do it [since I've never done it that way!!! ]

  14. #14
    whitelexi is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    20
    Quote Originally Posted by Robeen View Post
    You could go to the 'Add Existing Fields' button [towards the top right of your screen - just to the left of the Property Sheet button] and then drag & drop the new field onto your Form. Did you already try that?
    BUT . . . you should be able to do it by selecting the TextBox control - Property Sheet -> Data Tab -> click the down-arrow in the Control Source -> select the field you want to bind the control to.
    I don't understand why you used the Expression Builder to do it [since I've never done it that way!!! ]
    The Add Existing Fields only shows tables and their corresponding fields. I'm trying to bind a TextBox to a query... The problem seems to be that the TextBox is on a form which is already bound to a table and so the only way i can bind this TextBox to a query is by using the Expression Builder.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't bind a textbox to a query.

    Can use a domain aggregate function (DLookup, DSum, DAvg, etc) in textbox ControlSource to get a value from table or query not in the form or report RecordSource.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access 2003 automatic field entry
    By RANCHLAW56 in forum Forms
    Replies: 6
    Last Post: 12-30-2010, 02:57 PM
  2. Automatic Price for Booking - Access 2007
    By doquan0 in forum Queries
    Replies: 1
    Last Post: 08-27-2010, 11:34 AM
  3. Automatic Calculation in table
    By musicalogist in forum Access
    Replies: 4
    Last Post: 04-22-2010, 11:52 AM
  4. Automatic Report Generation - Access VBA
    By initiator in forum Programming
    Replies: 2
    Last Post: 04-19-2010, 05:10 AM
  5. Query DateDiff calculation excluding weekends
    By Masterfinn in forum Queries
    Replies: 3
    Last Post: 04-01-2010, 09:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums