Coping with missing year value in date data type field (Rephrased/Repost)

[Fred Boer]

Hi!

Suppose I am collecting Date of Birth data for a contacts application.
However, some contacts are only willing to provide day and month
information, but withold the year of birth. What's a good way to deal with
this? I can think of two, but...

1. Use a "dummy" value for an unknown year, (e.g. 1900). This would allow
the use of a date/time data type and be fairly easy to implement, but it
sure feels like I would be breaking some kind of relational rule to enter
"fake" data.

2. Create three text fields (txtDay, txtMonth, txtYear). This would allow
the use of "Null" for an unknown year (more correct "relationally", I
*think*...). But it would be more complicated to do age calculations and
sorting and the like, and would be more challenging to implement (Another
supposition, since I haven't tried it...)

Suggestions?

Thanks!
Fred Boer

P.S. Let's imagine that forcing compliance would break a business rule...

 

[Jeff Boyce]

Fred

If you don't have the year, how will you do "age calculation"?

I suppose one approach might be to allow input (a text field, not a
date/time field) of day and month, then use IsDate() to test. If the input
is a date, proceed with age calculation...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Douglas J. Steele]

Based on an exchange in another thread, I believe that when Fred knows the
person's birthday, but not his/her actual birthdate, he'll enter 1900-05-04
as DOB. That way, at least he'll be able to retrieve the records of all
contacts with a birthday on a specific date (although he won't be able to
calculate their age). He's obviously counting on the fact that he won't be
dealing with too many 107 year olds!

He uses IIf when calculating the age:

IIf(Year([DOB]) = 1900, 0, <standard age calculation>)

 

[Fred Boer]

Hi Jeff and Doug:

Jeff, my fault and sorry I wasn't more clear, but the age calculation isn't
the key here. It *is* calculated and displayed on a form, but the control
that displays the age has an expression to filter out unknown birth years
and display a blank. The main point of the this process is to create a
"birthday" reminder/tickler process, and also so that a report listing
birthdays by day and month (not age or year of birth) can be generated.

Yes, Doug, that's right, you saw that in the earlier thread. I'm trying to
help Sue, and I wasn't sure if I was giving her good advice with this
issue - hence this repost.

So is a "dummy" year an ok approach?

Thanks very much to both of you!
Fred

Based on an exchange in another thread, I believe that when Fred knows the
person's birthday, but not his/her actual birthdate, he'll enter
1900-05-04 as DOB. That way, at least he'll be able to retrieve the
records of all contacts with a birthday on a specific date (although he
won't be able to calculate their age). He's obviously counting on the fact
that he won't be dealing with too many 107 year olds!

He uses IIf when calculating the age:

IIf(Year([DOB]) = 1900, 0, <standard age calculation>)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Fred

If you don't have the year, how will you do "age calculation"?

I suppose one approach might be to allow input (a text field, not a
date/time field) of day and month, then use IsDate() to test. If the
input
is a date, proceed with age calculation...

 

[Douglas J. Steele]

My only concern is that it IS still possible for someone to have been born
in 1900.

As well, 1900 wasn't a leap year, so you would be unable to store
1900-02-29.

To avoid both of these issues, why not use 104 as the year, or 9996? (unless
you're afraid this will lead to a Y9996 crisis <g>)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Jeff and Doug:

Jeff, my fault and sorry I wasn't more clear, but the age calculation
isn't the key here. It *is* calculated and displayed on a form, but the
control that displays the age has an expression to filter out unknown
birth years and display a blank. The main point of the this process is to
create a "birthday" reminder/tickler process, and also so that a report
listing birthdays by day and month (not age or year of birth) can be
generated.

Yes, Doug, that's right, you saw that in the earlier thread. I'm trying to
help Sue, and I wasn't sure if I was giving her good advice with this
issue - hence this repost.

So is a "dummy" year an ok approach?

Thanks very much to both of you!
Fred

Based on an exchange in another thread, I believe that when Fred knows
the person's birthday, but not his/her actual birthdate, he'll enter
1900-05-04 as DOB. That way, at least he'll be able to retrieve the
records of all contacts with a birthday on a specific date (although he
won't be able to calculate their age). He's obviously counting on the
fact that he won't be dealing with too many 107 year olds!

He uses IIf when calculating the age:

IIf(Year([DOB]) = 1900, 0, <standard age calculation>)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Fred

If you don't have the year, how will you do "age calculation"?

I suppose one approach might be to allow input (a text field, not a
date/time field) of day and month, then use IsDate() to test. If the
input
is a date, proceed with age calculation...


Hi!

Suppose I am collecting Date of Birth data for a contacts application.
However, some contacts are only willing to provide day and month
information, but withold the year of birth. What's a good way to deal
with
this? I can think of two, but...

1. Use a "dummy" value for an unknown year, (e.g. 1900). This would
allow
the use of a date/time data type and be fairly easy to implement, but
it
sure feels like I would be breaking some kind of relational rule to
enter
"fake" data.

2. Create three text fields (txtDay, txtMonth, txtYear). This would
allow
the use of "Null" for an unknown year (more correct "relationally", I
*think*...). But it would be more complicated to do age calculations
and
sorting and the like, and would be more challenging to implement
(Another
supposition, since I haven't tried it...)

 

[Fred Boer]

I'm intending to be around then, aren't you? <g>

Thanks, Doug! I never thought about the leap year issue...

Fred

 

[Sue]

I'm with Fred - the leap year issue never crossed my mind.
Out of curiosity, was there a special reason why you selected "104" &
"9996"?
Thanks, all of you, for your input!

My only concern is that it IS still possible for someone to have been born
in 1900.

As well, 1900 wasn't a leap year, so you would be unable to store
1900-02-29.

To avoid both of these issues, why not use 104 as the year, or 9996?
(unless you're afraid this will lead to a Y9996 crisis <g>)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Jeff and Doug:

Jeff, my fault and sorry I wasn't more clear, but the age calculation
isn't the key here. It *is* calculated and displayed on a form, but the
control that displays the age has an expression to filter out unknown
birth years and display a blank. The main point of the this process is to
create a "birthday" reminder/tickler process, and also so that a report
listing birthdays by day and month (not age or year of birth) can be
generated.

Yes, Doug, that's right, you saw that in the earlier thread. I'm trying
to help Sue, and I wasn't sure if I was giving her good advice with this
issue - hence this repost.

So is a "dummy" year an ok approach?

Thanks very much to both of you!
Fred

Based on an exchange in another thread, I believe that when Fred knows
the person's birthday, but not his/her actual birthdate, he'll enter
1900-05-04 as DOB. That way, at least he'll be able to retrieve the
records of all contacts with a birthday on a specific date (although he
won't be able to calculate their age). He's obviously counting on the
fact that he won't be dealing with too many 107 year olds!

He uses IIf when calculating the age:

IIf(Year([DOB]) = 1900, 0, <standard age calculation>)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Fred

If you don't have the year, how will you do "age calculation"?

I suppose one approach might be to allow input (a text field, not a
date/time field) of day and month, then use IsDate() to test. If the
input
is a date, proceed with age calculation...


Hi!

Suppose I am collecting Date of Birth data for a contacts application.
However, some contacts are only willing to provide day and month
information, but withold the year of birth. What's a good way to deal
with
this? I can think of two, but...

1. Use a "dummy" value for an unknown year, (e.g. 1900). This would
allow
the use of a date/time data type and be fairly easy to implement, but
it
sure feels like I would be breaking some kind of relational rule to
enter
"fake" data.

2. Create three text fields (txtDay, txtMonth, txtYear). This would
allow
the use of "Null" for an unknown year (more correct "relationally", I
*think*...). But it would be more complicated to do age calculations
and
sorting and the like, and would be more challenging to implement
(Another
supposition, since I haven't tried it...)

 

[Fred Boer]

Hi Sue!

I think I can answer that. It's probably in the Help system somewhere, but I
figured it out by experiment...

If you enter 01/01/99 as a date, Access changes it to 01/01/1999.
If you enter 01/01/100, Access accepts it.
So 104 would be the first leap year acceptable to Access.
Likewise, Access can accept 01/01/9999, but draws the line at 01/01/10000 -
(not surprisingly!)
And so... 9996 would be the last leap year Access could recognize.

Fred

P.S. Working from a DIAL-UP connection!! (My high speed is down due to
faulty phone line!! I'm getting the shakes...) <g>

I'm with Fred - the leap year issue never crossed my mind.
Out of curiosity, was there a special reason why you selected "104" &
"9996"?
Thanks, all of you, for your input!

My only concern is that it IS still possible for someone to have been
born in 1900.

As well, 1900 wasn't a leap year, so you would be unable to store
1900-02-29.

To avoid both of these issues, why not use 104 as the year, or 9996?
(unless you're afraid this will lead to a Y9996 crisis <g>)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Jeff and Doug:

Jeff, my fault and sorry I wasn't more clear, but the age calculation
isn't the key here. It *is* calculated and displayed on a form, but the
control that displays the age has an expression to filter out unknown
birth years and display a blank. The main point of the this process is
to create a "birthday" reminder/tickler process, and also so that a
report listing birthdays by day and month (not age or year of birth) can
be generated.

Yes, Doug, that's right, you saw that in the earlier thread. I'm trying
to help Sue, and I wasn't sure if I was giving her good advice with this
issue - hence this repost.

So is a "dummy" year an ok approach?

Thanks very much to both of you!
Fred

Based on an exchange in another thread, I believe that when Fred knows
the person's birthday, but not his/her actual birthdate, he'll enter
1900-05-04 as DOB. That way, at least he'll be able to retrieve the
records of all contacts with a birthday on a specific date (although he
won't be able to calculate their age). He's obviously counting on the
fact that he won't be dealing with too many 107 year olds!

He uses IIf when calculating the age:

IIf(Year([DOB]) = 1900, 0, <standard age calculation>)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Fred

If you don't have the year, how will you do "age calculation"?

I suppose one approach might be to allow input (a text field, not a
date/time field) of day and month, then use IsDate() to test. If the
input
is a date, proceed with age calculation...


Hi!

Suppose I am collecting Date of Birth data for a contacts
application.
However, some contacts are only willing to provide day and month
information, but withold the year of birth. What's a good way to deal
with
this? I can think of two, but...

1. Use a "dummy" value for an unknown year, (e.g. 1900). This would
allow
the use of a date/time data type and be fairly easy to implement, but
it
sure feels like I would be breaking some kind of relational rule to
enter
"fake" data.

2. Create three text fields (txtDay, txtMonth, txtYear). This would
allow
the use of "Null" for an unknown year (more correct "relationally", I
*think*...). But it would be more complicated to do age calculations
and
sorting and the like, and would be more challenging to implement
(Another
supposition, since I haven't tried it...)

 

[Douglas J. Steele]

That's absolutely correct, Fred.

Access Date fields can hold 01/01/100 to 12/31/9999.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Sue!

I think I can answer that. It's probably in the Help system somewhere, but
I figured it out by experiment...

If you enter 01/01/99 as a date, Access changes it to 01/01/1999.
If you enter 01/01/100, Access accepts it.
So 104 would be the first leap year acceptable to Access.
Likewise, Access can accept 01/01/9999, but draws the line at
01/01/10000 - (not surprisingly!)
And so... 9996 would be the last leap year Access could recognize.

Fred

P.S. Working from a DIAL-UP connection!! (My high speed is down due to
faulty phone line!! I'm getting the shakes...) <g>

I'm with Fred - the leap year issue never crossed my mind.
Out of curiosity, was there a special reason why you selected "104" &
"9996"?
Thanks, all of you, for your input!

My only concern is that it IS still possible for someone to have been
born in 1900.

As well, 1900 wasn't a leap year, so you would be unable to store
1900-02-29.

To avoid both of these issues, why not use 104 as the year, or 9996?
(unless you're afraid this will lead to a Y9996 crisis <g>)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Jeff and Doug:

Jeff, my fault and sorry I wasn't more clear, but the age calculation
isn't the key here. It *is* calculated and displayed on a form, but the
control that displays the age has an expression to filter out unknown
birth years and display a blank. The main point of the this process is
to create a "birthday" reminder/tickler process, and also so that a
report listing birthdays by day and month (not age or year of birth)
can be generated.

Yes, Doug, that's right, you saw that in the earlier thread. I'm trying
to help Sue, and I wasn't sure if I was giving her good advice with
this issue - hence this repost.

So is a "dummy" year an ok approach?

Thanks very much to both of you!
Fred

message Based on an exchange in another thread, I believe that when Fred knows
the person's birthday, but not his/her actual birthdate, he'll enter
1900-05-04 as DOB. That way, at least he'll be able to retrieve the
records of all contacts with a birthday on a specific date (although
he won't be able to calculate their age). He's obviously counting on
the fact that he won't be dealing with too many 107 year olds!

He uses IIf when calculating the age:

IIf(Year([DOB]) = 1900, 0, <standard age calculation>)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Fred

If you don't have the year, how will you do "age calculation"?

I suppose one approach might be to allow input (a text field, not a
date/time field) of day and month, then use IsDate() to test. If the
input
is a date, proceed with age calculation...


Hi!

Suppose I am collecting Date of Birth data for a contacts
application.
However, some contacts are only willing to provide day and month
information, but withold the year of birth. What's a good way to
deal with
this? I can think of two, but...

1. Use a "dummy" value for an unknown year, (e.g. 1900). This would
allow
the use of a date/time data type and be fairly easy to implement,
but it
sure feels like I would be breaking some kind of relational rule to
enter
"fake" data.

2. Create three text fields (txtDay, txtMonth, txtYear). This would
allow
the use of "Null" for an unknown year (more correct "relationally",
I
*think*...). But it would be more complicated to do age calculations
and
sorting and the like, and would be more challenging to implement
(Another
supposition, since I haven't tried it...)

 

[Sue]

Thanks, Fred et al.
Makes sense.
104 it is! I may meet a 107-year-old born on 2/29, but I darn sure don't
expect to meet someone who's 1903 years old!
Good luck getting back to high-speed, Fred.

I'm with Fred - the leap year issue never crossed my mind.
Out of curiosity, was there a special reason why you selected "104" &
"9996"?
Thanks, all of you, for your input!

My only concern is that it IS still possible for someone to have been
born in 1900.

As well, 1900 wasn't a leap year, so you would be unable to store
1900-02-29.

To avoid both of these issues, why not use 104 as the year, or 9996?
(unless you're afraid this will lead to a Y9996 crisis <g>)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Jeff and Doug:

Jeff, my fault and sorry I wasn't more clear, but the age calculation
isn't the key here. It *is* calculated and displayed on a form, but the
control that displays the age has an expression to filter out unknown
birth years and display a blank. The main point of the this process is
to create a "birthday" reminder/tickler process, and also so that a
report listing birthdays by day and month (not age or year of birth) can
be generated.

Yes, Doug, that's right, you saw that in the earlier thread. I'm trying
to help Sue, and I wasn't sure if I was giving her good advice with this
issue - hence this repost.

So is a "dummy" year an ok approach?

Thanks very much to both of you!
Fred

Based on an exchange in another thread, I believe that when Fred knows
the person's birthday, but not his/her actual birthdate, he'll enter
1900-05-04 as DOB. That way, at least he'll be able to retrieve the
records of all contacts with a birthday on a specific date (although he
won't be able to calculate their age). He's obviously counting on the
fact that he won't be dealing with too many 107 year olds!

He uses IIf when calculating the age:

IIf(Year([DOB]) = 1900, 0, <standard age calculation>)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Fred

If you don't have the year, how will you do "age calculation"?

I suppose one approach might be to allow input (a text field, not a
date/time field) of day and month, then use IsDate() to test. If the
input
is a date, proceed with age calculation...


Hi!

Suppose I am collecting Date of Birth data for a contacts
application.
However, some contacts are only willing to provide day and month
information, but withold the year of birth. What's a good way to deal
with
this? I can think of two, but...

1. Use a "dummy" value for an unknown year, (e.g. 1900). This would
allow
the use of a date/time data type and be fairly easy to implement, but
it
sure feels like I would be breaking some kind of relational rule to
enter
"fake" data.

2. Create three text fields (txtDay, txtMonth, txtYear). This would
allow
the use of "Null" for an unknown year (more correct "relationally", I
*think*...). But it would be more complicated to do age calculations
and
sorting and the like, and would be more challenging to implement
(Another
supposition, since I haven't tried it...)

 

[John W. Vinson]

I may meet a 107-year-old born on 2/29, but I darn sure don't
expect to meet someone who's 1903 years old!

Hrumph. My beard was grey already in 104. And the Emperor graciously let me
manage his library...


John W. Vinson [MVP]