Re: Calculating age, given DOB & current date

Tech-Archive recommends: Fix windows errors by optimizing your registry

The usual formula is:

DateDiff("yyyy", [DOB], Date()) - _
IIf(Format(Date(), "mmdd") < Format([DOB], "mmdd"), 1, 0)

This is necessary because DateDiff is a little too literal in how it
calculates differences: to it, there's 1 year between 31 Dec, 2005 and 1
Jan, 2006. That's why you have to add the second part: to subtract one from
what DateDiff returns if the birthday hasn't happened yet this year.

Now, how were you planning on using this?

Hopefully, you know that you do not store Age in a table.

Your options are to create a query, and add the Age calculation as a
computed field in the query, or use it as the Control Source for a text box
on your form.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Paul Hyett" <pah@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:Gst$JGBUb29DFwdR@xxxxxxxxxxxxxxxxxxxxxxx
Hi, I'm a complete novice on Access, but with a lot of experience on
Excel.

How would I go about the above?
--
Paul 'Charts Fan' Hyett


.

Relevant Pages

  • Re: How do I calculate times over multiple records?
    ... The unfortunate thing is that queries with coordinated subqueries are often ... doing a sum on the DateDiff Calculation. ... query) and therefore runs once for each row returned by the outer query. ... Excluding Weekends and Holidays is more complex. ...
    (microsoft.public.access.queries)
  • Re: rounding in update query
    ... I have an update query that does a DateDiff ... it updates the chronological age column with values ... Sorry but DateDiff() does NOT give fractional values. ... Where is the birthdate field. ...
    (microsoft.public.access.queries)
  • Re: date question
    ... triggers because I don't know what they are, ... ever use DATEDIFF in any query at all -- rip that page out of Books ... Online, and pretend it never existed, since clearly no query could perform ... I hope for you joy and time to reflect and do not let ...
    (microsoft.public.sqlserver.programming)
  • Re: Beetle - I Need You Again
    ... DateDiff is a VERY SIMPLE function, so I don't know why you are ... membership start date field exists), and maybe we can go form there. ... second task would be how to use it in a query. ... you don't need to slice and dice ANYTHING. ...
    (microsoft.public.access.formscoding)
  • Re: Cant get SUM to work
    ... mispercetion of what DateDiff does. ... try to restrict your query down to as ... I have put this field from the query on the report "ReceiptOfGoodsToNow". ... txtReceiptOfGoodsToNow" and was getting a parameter prompt for the name of ...
    (microsoft.public.access.reports)