Collecting personal information on forms

[Scooter]

One of my forms (surveys) asks for a DOB via the standard mo/dd/yyyy. When I
enter birth years prior to say 1948 it calculates it as a -# for example 1928
shows up as a -11 when calculating into actual age. This is my query string:
a=(DateDiff("d",[BI-3],Now()))/365

Is there cut off for the birth year and if so can changing the VBC fix this?

 

[John W. Vinson]

One of my forms (surveys) asks for a DOB via the standard mo/dd/yyyy. When I
enter birth years prior to say 1948 it calculates it as a -# for example 1928
shows up as a -11 when calculating into actual age. This is my query string:
a=(DateDiff("d",[BI-3],Now()))/365

Is there cut off for the birth year and if so can changing the VBC fix this?

Your algorithm doesn't account for leapyears and has many other problems! Try
instead

DateDiff("yyyy", [BI-3], Date()) - IIF(Format([BI-3], "mmdd") > Format(Date(),
"mmdd"), 1, 0)

This calculates the number of years between the dates, and corrects for cases
where this year's birthday anniversary has not been reached.

Are you in fact insisting on four-digit years? You should: if you enter (say)
10/15/29 in the [BI-3] field, Access will interpret it as 10/15/2029. Two
digit years from 00 through 29 are interpreted as the current century, 30
through 99 as the previous century - so if you have a birthdate such as
5/23/1898 or actually anything prior to 1/1/1930, you'll be in trouble.

 

[Scooter]

John: thank you so much for your reply. I am asking for a 4-digit years and
yes, the problem occurs for birthdates prior to 1930. Is there a solution
for this or am I stuck with these options? Is it perhaps in the VBA?
Thank you again for you timely response.

One of my forms (surveys) asks for a DOB via the standard mo/dd/yyyy. When I
enter birth years prior to say 1948 it calculates it as a -# for example 1928
shows up as a -11 when calculating into actual age. This is my query string:
a=(DateDiff("d",[BI-3],Now()))/365

Is there cut off for the birth year and if so can changing the VBC fix this?

Your algorithm doesn't account for leapyears and has many other problems! Try
instead

DateDiff("yyyy", [BI-3], Date()) - IIF(Format([BI-3], "mmdd") > Format(Date(),
"mmdd"), 1, 0)

This calculates the number of years between the dates, and corrects for cases
where this year's birthday anniversary has not been reached.

Are you in fact insisting on four-digit years? You should: if you enter (say)
10/15/29 in the [BI-3] field, Access will interpret it as 10/15/2029. Two
digit years from 00 through 29 are interpreted as the current century, 30
through 99 as the previous century - so if you have a birthdate such as
5/23/1898 or actually anything prior to 1/1/1930, you'll be in trouble.

 

[John W. Vinson]

John: thank you so much for your reply. I am asking for a 4-digit years and
yes, the problem occurs for birthdates prior to 1930. Is there a solution
for this or am I stuck with these options? Is it perhaps in the VBA?
Thank you again for you timely response.

The code WILL calculate a correct birthdate if the date has four digit years.
What is actually in the [MI-3] field if you look in the table? Does it contain
dates in 1929, or (as I'm guessing) in 2029? How is the date being entered? Do
you have an input mask such as 00/00/0000 on the field, or can users enter
(e.g.) 5/22/29 (and then not notice that it's putting the date in the wrong
century)?