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

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

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?