MVPs - Ideas to store month and date of birth only, no year?

[Rick B]

Please see 'dumb blondes' post in the queries newsgroup and add your two
cents if you have any sugestions. She wants to store Month and Date only.
Currently is doing so as a text field and then translating the first two
characters into an alphnumeric month abreviation and using that for sorting
and queries.

Quite a mess, but not confident enough in my suggestion to help get it
closer to normal.

Rick B

 

[Duane Hookom]

If you don't want to store a date, then I would suggest two fields:
BirthMonth integer 1-12
BirthDay integer 1-31

 

[Tim Ferguson]

She wants to store Month and Date only.
Currently is doing so as a text field and then translating the first
two characters into an alphnumeric month abreviation and using that
for sorting and queries.

It can be quite hard to validate fields like that.

One alternative: use a single Long Integer to store (12 * YearNum +
MonthNum) -- the UI is harder to program but it's easy to sort, do maths,
convert into a real date, and so on.

Another alternative: use two integer fields to store YearNum and MonthNum.
User interface is easier to program, but probably less intuitive for the
user. Easy to validate, also pretty easy to sort, key, do maths, etc etc.

If you do go for a text field, it really needs to be formatted yyyy-mm
because sorting and selecting will be really difficult with anything else.
Unfortunately, in most countries this is not a natural date format and it
may meet with some resistance from users.

In short, there is no easy answer for manipulating date ranges. In long,
the best answer would be a custom ActiveX control. It strikes me that this
is almost a FAQ and wouldn't it be worth someone's while creating a select-
a-month control for the Access community?

Hope that helps


Tim F

 

[Tim Ferguson]

Hope that helps

Well, pretty obviously it didn't... you were asking about month/day, not
year/month. My excuse is that it's late on this side of the Atlantic.

Most of what I said holds true in principle, but is more complex because
the validation is harder. For example, the value "11/31" is always illegal,
and so is "02/30" but what about "02/29"? Okay this year? Okay next year?

I still would steer well away from text values because they are impossible
to sort and nearly impossible to validate. Using two integer fields, you
can use the DateSerial function to test for a valid date (but pick the
dummy year value appropriately). Using one integer for day-of-the-year is
easy to convert using DateSerial one way, and DatePart("y"...) the other,
but again you have to decide up front what you are doing about the end of
February and, by extension, every day in the year following.

Duhhh... sorry :-(


Tim F

 

[Craig Hornish]

Ok I am not a MVP, and I couldn't find 'dumb blondes' but I will attempt an
answer from what you posted.

If it is a birthdate then why not just use a date field - if year is
not wanted then just use a fake one (1904 - so you can have a 2/29). This
takes care of your validation needs.

Now you can use the date functions for sorting.
In a query
SELECT BDate.BirthDate, Month([BirthDate]) AS Expr2, Day([BirthDate]) AS
Expr1
FROM BDate
ORDER BY Month([BirthDate]), Day([BirthDate]);

I have no problems with the fake year, if the application doesn't need a
year then it probably small enough that it shouldn't matter.

I believe KISS applies here - why should you deal with the hazzards of
two text boxes, and the validation that goes with splitting a date.

Craig Hornish
(e-mail address removed) - so I can delete it when it becomes a spam magnet

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"