Storing birthdate with year optional

[User]

I need to store a birth date in a table mainly to keep track of when to
celebrate birthdays. Some people will offer their complete birth date
including the year, but many will only want to offer the day and month
part of their birth date. For the most part I only need the day and
month, although I'd hate to throwaway year information for the people
who give it. Any thoughts on how to handle this situation?

 

[Geoff]

The Date/Time data type can store date and time values from the years 100 to
9999.

Use 100 for the year when the year is unknown. When you want to determine
whether you've got the real year of birth, extract the year and see if it's
not 100.

Geoff

 

[Ken Sheridan]

If you follow Geoff's suggestion use 104 not 100 as the year. That would be
a leap year if the Julian calendar were extended back to then, so will allow
birthday's on 29 February.

Alternatively you can store the day, month and year in separate columns in
the table, allowing Nulls (Required property = True) in the year and
disallowing them for the day and month. Also don't give the columns a
DefaultValue. Then use the DateSerial function to return actual dates, e.g.

If DateSerial(Year(VBA.Date),DoB_Month,Dob_Day) = VBA.Date Then
MsgBox "Happy Birthday"
End If

Note that in years which are not a leap year the expression would evaluate
to True on 1 March for anyone with a birthday on 29 February. I think such
people usually celebrate their birthdays on 28 February in non leap years,
however, so you might want to take account of that, which you can do with a
simple function:

Public Function IsBirthday(intMonth As Integer, _
intDay As Integer, _
Optional varDateAt As Variant) As Boolean

Dim n As Integer

' set current date as default
If IsMissing(varDateAt) Then
varDateAt = VBA.Date
End If

' is birthday on 29 Feb
If intMonth = 2 And intDay = 29 Then
' if current year not a leap year make birthday 28 Feb
If Day(DateSerial(Year(varDateAt), 2, 29)) <> 29 Then
n = 1
End If
End If

IsBirthday = DateSerial(Year(varDateAt), intMonth, intDay - n) = varDateAt

End Function

Paste it into a standard module and call it by passing the month and day
values and an optional date value to determine on which date you want the
function to return True if its someone's birthday. This defaults to the
current date, so if you are using separate columns for the day, month and
year toy could list all employees whose birthday is today in a query along
these lines:

SELECT *
FROM Employees
WHERE IsBirthday(DoB_Month,DoB_Day) = TRUE;

where DoB_Month and DoB_Day are the column names. If a single date/time
column, DoB say, is used, with a dummy year of 104 for the sensitive
employees then it would be:

SELECT *
FROM Employees
WHERE IsBirthday(MONTH(DoB),DAY(DoB)) = TRUE;

To specify a specific date rather than using the current date add the extra
optional argument, e.g.

SELECT *
FROM Employees
WHERE IsBirthday(DoB_Month,DoB_Day,#07/04/2006#) = TRUE;

to return employees born on 4th July.

Ken Sheridan
Stafford, England

 

[User]

Thanks, both of those ideas sound good. I'm definitely looking for the
simpler solution which at first glance seems to be storing the year 104
for unknown years. How do I implement this scheme using a textbox on a
form?

 

[Geoff]

I assume the Textbox on the form is bound to a Date/Time field in a table.

Follow these steps:

1. Open the form in design view.
2. Right-click the Textbox and select Properties from the right-click
menu.
3. In the Properties dialog that opens, click the Format tab (at the top
of the dialog).
4. Click in the Format property and click the down arrow that appears on
the right.
5. Select "Short Date".
6. Click the Data tab (at the top of the dialog).
7. Click in the Input Mask property and click its build button (the three
dots ... on the right).
8. In the Input Mask Wizard that appears, select Short Date and click
Finish.
9. Close the Properties dialog.
10. Save the form and try it out.

Just enter the date of birth in the Textbox as prompted, eg 1/1/104.

Geoff

 

[User]

Just enter the date of birth in the Textbox as prompted, eg 1/1/104.

Ah, but I don't want the user to have to remember/know that it's
necessary to put in 104 when the year is unknown. I want it to be such
that if the user enters, for example, 3/5/1967 then that is the date
that is stored and if the user enters 3/5 then 3/5/104 is stored.

 

[John Vinson]

Ah, but I don't want the user to have to remember/know that it's
necessary to put in 104 when the year is unknown. I want it to be such
that if the user enters, for example, 3/5/1967 then that is the date
that is stored and if the user enters 3/5 then 3/5/104 is stored.

Two ways I can think of to do that. By default, if you enter a date
with a single slash (3/5) which can be correctly interpreted as a
month and a day, Access will fill in the current year. IF you will
NEVER be entering birthdates for babies under a year old, you can take
advantage of this fact in the textbox's AfterUpdate event:

Private Sub txtBirthdate_AfterUpdate()
If Year(Me.txtBirthdate) = Year(Date) Then
Me.txtBirthdate = DateSerial(104, _
Month(Me.txtBirthdate), Day(Me.txtBirthdate))
End If
End Sub

The alternative would be to use an unbound textbox with code to check
for the number of slashes... more code than I want to write off the
top of my head this afternoon!

John W. Vinson[MVP]

 

[Douglas J. Steele]

Two ways I can think of to do that. By default, if you enter a date
with a single slash (3/5) which can be correctly interpreted as a
month and a day, Access will fill in the current year. IF you will
NEVER be entering birthdates for babies under a year old, you can take
advantage of this fact in the textbox's AfterUpdate event:

Private Sub txtBirthdate_AfterUpdate()
If Year(Me.txtBirthdate) = Year(Date) Then
Me.txtBirthdate = DateSerial(104, _
Month(Me.txtBirthdate), Day(Me.txtBirthdate))
End If
End Sub

The alternative would be to use an unbound textbox with code to check
for the number of slashes... more code than I want to write off the
top of my head this afternoon!

Use the Split function. Assuming the unbound textbox is named MyTextField,
and there's a bound textbox txtBirthday (which needn't be visible...):

Private Sub MyTextField_AfterUpdate()

Dim varDates As Variant

varDates = Split(Me.MyTextField, "/")

' varDates will now be an array containing the values
' in MyTextField without the slashes.
' For instance, 3/5 will result in varDates(0) being equal
' to 3, and varDates(1) being equal to 5, while
' 3/5/1967 will result in varDates(0) being equal to
' 3, varDates(1) being equal to 5, and varDates(2)
' being equal to 1967.

Select Case UBound(varDates)
Case 0
' No slashes in what was input
MsgBox "You didn't enter a valid date."
Case 1
' 1 slashes: assume only month and day
Me.txtBirthday = DateSerial(104, varDates(0), varDates(1))
Case 2
' 2 slashes: assume month, day, year
Me.txtBirthday = DateSerial(varDates(2), varDates(0), varDates(1))
Case Else
' Someone needs to learn how to type...
MsgBox "You didn't enter a valid date."
End Select

End Sub

Note that this assumes Access 2000 or newer.

 

[John Vinson]

Use the Split function. Assuming the unbound textbox is named MyTextField,
and there's a bound textbox txtBirthday (which needn't be visible...):

<grateful bow>


John W. Vinson[MVP]

 

[Geoff]

Have your users thought about using Microsoft Outlook's calendar?

Outlook's calendar allows you to enter a birthday (without its year), set
its recurrence to be annually, and set a reminder for say 1 or 2 weeks
before the event, to give you time to buy a card and present. You could
stills store the year in the notes of the calendar item.

Just a thought...
Geoff

 

[User]

As it happens, keeping track of the birth date is only a small part of
this particular application.

 

[User]

Two ways I can think of to do that. By default, if you enter a date
with a single slash (3/5) which can be correctly interpreted as a
month and a day, Access will fill in the current year. IF you will
NEVER be entering birthdates for babies under a year old, you can take
advantage of this fact in the textbox's AfterUpdate event:

Private Sub txtBirthdate_AfterUpdate()
If Year(Me.txtBirthdate) = Year(Date) Then
Me.txtBirthdate = DateSerial(104, _
Month(Me.txtBirthdate), Day(Me.txtBirthdate))
End If
End Sub

Thanks for all the solutions guys, I appreciate it. It's reasonable to
assume that babies under 1 year old will never be part of this
application, so I think I like this solution best because it's slightly
simpler than parsing the textbox myself.

The only question I have about this is regarding the display of the text
box. I'd like the application to display the birthday with year if it's
a valid year, and if it's 104 then I'd like it just to display the month
and day.

 

[John Vinson]

The only question I have about this is regarding the display of the text
box. I'd like the application to display the birthday with year if it's
a valid year, and if it's 104 then I'd like it just to display the month
and day.

You can't do that easily with a Format property. The only way I can
think of would be to use a calculated (not updateable) field in a
Query:

IIF([Birthdate] < #1/1/105#, Format([Birthday], "mm/dd"),
Format([Birthday], "mm/dd/yyyy"))

John W. Vinson[MVP]