By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,391 Members | 1,653 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,391 IT Pros & Developers. It's quick & easy.

datediff question

P: n/a
i have a question regarding the DateDiff function. I am quite new to access
and seem to have hit a snag with this function. My problem is as follows:

I have a table field that list a persons DateOfBirth. The standard format
for this field is yy.mm.dd (ie- 73.02.01). And on the form that the field is
brought into the input mask for the field is 00.99.99;0;_

What i want to do is have another field on the form that generates the
persons Age. Now, i checked out Microsofts website and they list an example
that makes the Birthdate filed be set to Short Date and for the Age field to
have the following control source:
=DateDiff("yyyy", [Birthdate], Now())+ Int( Format(now(), "mmdd") <
Format( [Birthdate], "mmdd") )

Now, this will generate a number but the only problem
is that for a birthdate of 73.02.01 it will display 31, but for a
birthdate of 28.03.02 it will generate a negative number.

How can I rectify this problem? Your input is appreciated.


Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"chanchito_cojones" <po******@noemail.net> wrote in
news:uy*********************@twister01.bloor.is.ne t.cable.rogers.
com:
i have a question regarding the DateDiff function. I am quite
new to access and seem to have hit a snag with this function.
My problem is as follows:

I have a table field that list a persons DateOfBirth. The
standard format for this field is yy.mm.dd (ie- 73.02.01). And
on the form that the field is brought into the input mask for
the field is 00.99.99;0;_

What i want to do is have another field on the form that
generates the persons Age. Now, i checked out Microsofts
website and they list an example that makes the Birthdate
filed be set to Short Date and for the Age field to have the
following control source: =DateDiff("yyyy", [Birthdate],
Now())+ Int( Format(now(), "mmdd") < Format( [Birthdate],
"mmdd") )

Now, this will generate a number but the only problem
is that for a birthdate of 73.02.01 it will display 31, but
for a birthdate of 28.03.02 it will generate a negative
number.

How can I rectify this problem? Your input is appreciated.

If the negative number is 24, the problem is one of Access
automatically (mis)setting the century.
Set the input mask of the birthdate field to be 9999.99.99 so
that you can force the correct century.

Access seems to think that two digit years <30 belong in the 21st
century, and >-30 belong in the old millenium.

Bob Quintal
Nov 13 '05 #2

P: n/a
Thanks bob. I appreciate the help. Let me just verify that I understand what
you are saying. Pretty much there is no way I would be able to use the Age
function with a 2digit version of the Year. It has to be in the complete
four digits or it won't work. Correct? Because if so that would really
suck. hehe. Oh well.

thanks again.
"Bob Quintal" <bq******@generation.net> wrote in message
news:76******************************@news.teranew s.com...
"chanchito_cojones" <po******@noemail.net> wrote in
news:uy*********************@twister01.bloor.is.ne t.cable.rogers.
com:
i have a question regarding the DateDiff function. I am quite
new to access and seem to have hit a snag with this function.
My problem is as follows:

I have a table field that list a persons DateOfBirth. The
standard format for this field is yy.mm.dd (ie- 73.02.01). And
on the form that the field is brought into the input mask for
the field is 00.99.99;0;_

What i want to do is have another field on the form that
generates the persons Age. Now, i checked out Microsofts
website and they list an example that makes the Birthdate
filed be set to Short Date and for the Age field to have the
following control source: =DateDiff("yyyy", [Birthdate],
Now())+ Int( Format(now(), "mmdd") < Format( [Birthdate],
"mmdd") )

Now, this will generate a number but the only problem
is that for a birthdate of 73.02.01 it will display 31, but
for a birthdate of 28.03.02 it will generate a negative
number.

How can I rectify this problem? Your input is appreciated.

If the negative number is 24, the problem is one of Access
automatically (mis)setting the century.
Set the input mask of the birthdate field to be 9999.99.99 so
that you can force the correct century.

Access seems to think that two digit years <30 belong in the 21st
century, and >-30 belong in the old millenium.

Bob Quintal

Nov 13 '05 #3

P: n/a
"chanchito_cojones" <po******@noemail.net> wrote in
news:NG*********************@twister01.bloor.is.ne t.cable.rogers.
com:
Thanks bob. I appreciate the help. Let me just verify that I
understand what you are saying. Pretty much there is no way I
would be able to use the Age function with a 2digit version of
the Year. It has to be in the complete four digits or it won't
work. Correct? Because if so that would really suck. hehe. Oh
well.

thanks again.

You could work around the problem by testing if the birthdate is
future, and subtract 100 years. That will work until you enter
the birthdate of someone who is 201 years old. [:^?

Or test if age <0, then age = age+100

Bob Quintal

"Bob Quintal" <bq******@generation.net> wrote in message
news:76******************************@news.teranew s.com...
"chanchito_cojones" <po******@noemail.net> wrote in
news:uy*********************@twister01.bloor.is.ne t.cable.roge
rs. com:
> i have a question regarding the DateDiff function. I am
> quite new to access and seem to have hit a snag with this
> function. My problem is as follows:
>
> I have a table field that list a persons DateOfBirth. The
> standard format for this field is yy.mm.dd (ie- 73.02.01).
> And on the form that the field is brought into the input
> mask for the field is 00.99.99;0;_
>
> What i want to do is have another field on the form that
> generates the persons Age. Now, i checked out Microsofts
> website and they list an example that makes the Birthdate
> filed be set to Short Date and for the Age field to have
> the following control source: =DateDiff("yyyy",
> [Birthdate], Now())+ Int( Format(now(), "mmdd") < Format(
> [Birthdate], "mmdd") )
>
> Now, this will generate a number but the only problem
> is that for a birthdate of 73.02.01 it will display 31, but
> for a birthdate of 28.03.02 it will generate a negative
> number.
>
> How can I rectify this problem? Your input is appreciated.
>

If the negative number is 24, the problem is one of Access
automatically (mis)setting the century.
Set the input mask of the birthdate field to be 9999.99.99 so
that you can force the correct century.

Access seems to think that two digit years <30 belong in the
21st century, and >-30 belong in the old millenium.

Bob Quintal



Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.