RE: Storing birthdate with year optional
- From: Ken Sheridan <KenSheridan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 19 Nov 2006 09:14:01 -0800
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" wrote:
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?
.
- References:
- Storing birthdate with year optional
- From: User
- Storing birthdate with year optional
- Prev by Date: Re: Updating backend db
- Next by Date: Re: Question about dating
- Previous by thread: Re: Storing birthdate with year optional
- Next by thread: Re: Storing birthdate with year optional
- Index(es):
Relevant Pages
|
