If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
MVPs - Ideas to store month and date of birth only, no year?
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 |
#2
|
|||
|
|||
If you don't want to store a date, then I would suggest two fields:
BirthMonth integer 1-12 BirthDay integer 1-31 -- Duane Hookom MS Access MVP -- "Rick B" Anonymous wrote in message ... 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 |
#3
|
|||
|
|||
"Rick B" Anonymous wrote in :
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 |
#4
|
|||
|
|||
Tim Ferguson wrote in
: 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 |
#5
|
|||
|
|||
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 - 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" "Rick B" Anonymous wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query week to date, month to date, year to date hours | Ty | Running & Setting Up Queries | 1 | December 15th, 2004 03:46 AM |
QDE (Quick Date Entry) | Norman Harker | Worksheet Functions | 37 | September 5th, 2004 01:24 AM |
QDE (Quick Date Entry) | Norman Harker | General Discussion | 3 | September 3rd, 2004 08:00 AM |
Aggregating Date Data into Weeks and Quarters | Roger | Running & Setting Up Queries | 3 | July 11th, 2004 05:56 PM |
date conversion | web_time | Worksheet Functions | 10 | December 25th, 2003 06:51 PM |