Hi,
I have a table where I'd like to store birthdays of contacts, however - most of the times - I do NOT have the year.

like
Name - Tom Brown
Birthday - 28 September

How should I store this info in access? As a text data type? I don't want the year, only MONTH and DAY

The main purpose of this field is to be able to set up a birthday reminder. Is there a better way of doing it?

P.S.: I will NOT be able to get the YEAR of birth from my contacts.

Thanks so much for any suggestions.

Rick

Does the year matter?
If not, you could arbitrarily assign any year to go along with the day and month. That way, you could get a reminder whenever just the day and month stored match the current day and month.

I thought about that too but I was trying to find a "cleaner" solution. Thanks for your suggestion though.

I thought about that too but I was trying to find a "cleaner" solution. Thanks for your suggestion though.
'Cleaner' in what way?

Using the current year, or just pick a year does not seem 100% right. Again, I not saying that I am not going to use this solution but I was wondering if there was a better way to handle it.

Using the current year, or just pick a year does not seem 100% right. Again, I not saying that I am not going to use this solution but I was wondering if there was a better way to handle it.
Another option would be to store the data as text and do a conversion when you need the comparison.
If you don't want to store a year at all, that would be 'cleaner'.

Assuming you enter birthday as 28 September in one field it would need to be a text field.

You could try this way.

A Right() and Left() will split the 28 September into two fields.

Make two other calculated fields for MNow: MonthName(Month(Date())) and DNow: Day(Date())

The first one give the current month as a word, that is, May and the second one will give the day number.

IIF will now give you a match.

IIF([BDay]=[CurrentDay] And [BMon]=[CurrentMth],1,Null) All the people with a birthday TODAY will have a 1 and the others will be null or whatever answers you want.

You could shorten it by leaving out the Right() and Left() and join MonthName(Month(Date())) and DNow: Day(Date()) which will give 10 May in one field and then do IIF for that field matching your birthday field. However, that will require that you always enter the birthday the same way with respect to spaces.

Here is a little example for you and doing it both ways. Just open Query1

I have 10 May for a current birthday because it is now 10 may in Australia. So change the date entries to suit your time

I just noticed I should have put LTrim() for the RightBDay calc field. If that is that is done it does not matter if you have some entries wityh double space between day and month

I guess I like this solution a lot. I'm going to try and let you know.
Thanks so much for your precious input.

Hi-

Birthday is the day (mmmmdd) we celebrate the anniversary of our birth. No year is necessary unless we're calculating retirement date, etc. ... then we use birthdate.

Why not a calculated date/time field that shows the upcoming birthday, including the year.


Public Function Anniversary2(ByVal pdate As Variant) As Date
'*******************************************
'Name: Anniversary2
'Purpose: Return this years' or the next
' anniversary of an input date
'Source: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=48683
'Author: raskew
'*******************************************

Dim myDate As Date

myDate = DateValue(pdate)

' this line provides the anniversary date occuring this year
myDate = DateSerial(year(Date), month(myDate), Day(myDate))

' this line specifies the next scheduled anniversary
' if this years' anniversary has already past

Anniversary2 = DateAdd("yyyy", IIf(myDate < Date, 1, 0), myDate)

End Function

Test it from the debug (immediate) window:

? anniversary2("25 September")
9/25/2008

? anniversary2(#4/1/69#)
4/1/2009

HTH - Bob

Bob

That is real neat....and I tested for real:D

Only needs Date() for criteria and all in one column.

Doing this for a calculated field also gets it in one column

BDmatch: (Day([BDay]) & "" & Month([BDay]))-(Day((Date())) & "" & Month((Date())))

The value is 0 on the birthday. Enter 0 in the criteria for the calculated field and you will get the records for people who have a birthday. Like Bob's it won't matter if you double space your entry.

So you now you have two options to do it off one column:)

Edit: Back to drawing board:D 1 December and 12 February give the same result

Edit: This gets it:) IIf((MonthName(Month(Date())))=(LTrim(Right(Trim([BDay]),Len(Trim([BDay]))-InStr(1,[BDay]," ")))) And (Day(Date()))=(Left([BDay],InStr(1,[BDay]," ")-1)),0,Null) and use 0 for criteria row.