Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Location
    Rhyl
    Posts
    10

    Unanswered: Calculating age from date of birth

    Hi,

    I have tried adding in people's ages from their date of birth. I've added a new text box into the form and named it age. I've added the following as the Control Source:

    =DateDiff("yyyy",[Date of Birth],Now())+Int(Format(Now(),"mmdd")<Format([Date of Birth],"mmdd"))

    This has worked perfectly, however when I try to scroll through the records, the following error message appears:
    'The value you entered isn't valid for this field.
    For example, you may have entered text in a numeric field or a number that is larger than the FieldSize permits.'

    Please can anyone help? Thank you!

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I would do it this way, if it helps
    Code:
    ==DateDiff("yyyy",[DOB],Now())-IIf(Month(Now())<Month([DOB]),1,IIf(Month(Now())=Month([DOB]) And Day(Now())<Day([DOB]),1,0))
    ???

    MTB
    Last edited by MikeTheBike; 02-10-12 at 09:34. Reason: Corrected > to < (twice!)

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Both formulas are valid so I expect the error is coming from something else.

    • Exactly how are you 'scrolling through Records?'
    • Is this a Single View Form (seeing one Record at a time)?
    • Does the Error appear immediately you start to scroll?
    • Is the [Date of Birth] Field defined as a Date/Time Datatype rather than Text?

    I wonder if there is a Record which doesn't have a valid Date entered or perhaps one with nothing entered in the [Date of Birth] Field and the Error is popping when you reach this Record in your scroll.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Oct 2011
    Location
    Rhyl
    Posts
    10
    Hello Missinglinq,

    When I scroll through records using the 'Previous' or 'Next' command buttons, the error message does not appear, however when I scroll using the mouse scroll button, the error appears immediately after I scroll. It is in Single View Form, and I've changed the Date of Birth field to text, however this has not resolved the problem. All fields have got a valid Date entered.

    Is there any way I could maybe prevent users from scrolling using the mouse scroll button?

    Thanks!

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by cefndyengineering View Post
    Hello Missinglinq,

    When I scroll through records using the 'Previous' or 'Next' command buttons, the error message does not appear, however when I scroll using the mouse scroll button, the error appears immediately after I scroll. It is in Single View Form, and I've changed the Date of Birth field to text, however this has not resolved the problem. All fields have got a valid Date entered.

    Is there any way I could maybe prevent users from scrolling using the mouse scroll button?

    Thanks!
    The Field should be defined as Date/Time; sometimes people define them as Text and that can lead to problems.

    The Mouse Wheel has created havoc with Access apps since its invention, which is why for years many developers have disabled it, using a Stephen Lebans custom hack to do so. Microsoft finally addressed the issue with a native setting to do the same thing, with versions 2007/2010.

    The best I can figure out, from yours as well as other posts I've seen over the years, scrolling the Records as rapidly as the wheel is capable of simply outstrips Access' ability to run the calculations and so an error is popped. The fact that the error doesn't really seem to address the problem at hand is not really surprising; Access error messages frequently don't!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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