access

  • Thread starter Thread starter james
  • Start date Start date
J

james

My Access database is a membership database, whereby each
member is assigned a seperate form containing personal
details, including date of birth. How do I get the current
age displayed automatically in a seperate field when
inputting the member's date of birth?
 
I use a simple IF comparison to calculate age. First I subtract the 2
years. This will give me the approximate age. Then I compare months. If
the current month is less then the month of the birthday I subtract 1 from
the age and I'm done. If the current month is greater, then I have their
age already. If the current month is equal I check the day of month. If
the current day is less than the birthday, I subtract 1 from the age and I'm
done. If the current day is greater than or equal, that is the age. I have
it set up as a function so I just pass the birthday and the current date. I
can also check the date versus any other date using this method. Just make
sure to check if the date to compare to is less than the birthdate so you
don't get a negative age.

Kelvin
 
I use a simple IF comparison to calculate age. First I subtract the 2
years. This will give me the approximate age. Then I compare months. If
the current month is less then the month of the birthday I subtract 1 from
the age and I'm done. If the current month is greater, then I have their
age already. If the current month is equal I check the day of month. If
the current day is less than the birthday, I subtract 1 from the age and I'm
done. If the current day is greater than or equal, that is the age. I have
it set up as a function so I just pass the birthday and the current date. I
can also check the date versus any other date using this method. Just make
sure to check if the date to compare to is less than the birthdate so you
don't get a negative age.
Click to expand...

Sounds awfully elaborate. A single line will include all the same
considerations:

DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)
 
That is simpler code. My method had a lot of format() calls. I didn't
think about checking "mmdd" as one item. That's one logic step less than
what I've been doing.

John Vinson said:
I use a simple IF comparison to calculate age. First I subtract the 2
years. This will give me the approximate age. Then I compare months. If
the current month is less then the month of the birthday I subtract 1 from
the age and I'm done. If the current month is greater, then I have their
age already. If the current month is equal I check the day of month. If
the current day is less than the birthday, I subtract 1 from the age and I'm
done. If the current day is greater than or equal, that is the age. I have
it set up as a function so I just pass the birthday and the current date. I
can also check the date versus any other date using this method. Just make
sure to check if the date to compare to is less than the birthdate so you
don't get a negative age.
Click to expand...

Sounds awfully elaborate. A single line will include all the same
considerations:

DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)
Click to expand...
 
Back
Top