datediff question

[chanchito_cojones]

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.

[Bob Quintal]

Re: datediff question

"chanchito_cojo nes" <postonly@noema il.net> wrote in
news:uyPuc.2562 65$0qd.245620@t wister01.bloor. is.net.cable.ro gers.
com:
[color=blue]
> 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.
>[/color]
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

[chanchito_cojones]

Re: datediff question

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" <bquintal@gener ation.net> wrote in message
news:76a030e9e2 f2cfe2c42fa520a 6e5132b@news.te ranews.com...[color=blue]
> "chanchito_cojo nes" <postonly@noema il.net> wrote in
> news:uyPuc.2562 65$0qd.245620@t wister01.bloor. is.net.cable.ro gers.
> com:
>[color=green]
> > 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.
> >[/color]
> 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
>
>[/color]

[Bob Quintal]

Re: datediff question

"chanchito_cojo nes" <postonly@noema il.net> wrote in
news:NGEvc.3382 70$0qd.267295@t wister01.bloor. is.net.cable.ro gers.
com:
[color=blue]
> 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.
>[/color]

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
[color=blue]
>
> "Bob Quintal" <bquintal@gener ation.net> wrote in message
> news:76a030e9e2 f2cfe2c42fa520a 6e5132b@news.te ranews.com...[color=green]
>> "chanchito_cojo nes" <postonly@noema il.net> wrote in
>> news:uyPuc.2562 65$0qd.245620@t wister01.bloor. is.net.cable.ro ge
>> rs. com:
>>[color=darkred]
>> > 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.
>> >[/color]
>> 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
>>
>>[/color]
>
>
>[/color]