 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2007Coping with missing year value in date data type field (Rephrased/Repost)
Hi! Suppose I am collecting Date of Birth data for a contacts application. However, some contacts are only willing to provide day and month information, but withold the year of birth. What's a good way to deal with this? I can think of two, but... 1. Use a "dummy" value for an unknown year, (e.g. 1900). This would allow the use of a date/time data type and be fairly easy to implement, but it sure feels like I would be breaking some kind of relational rule to enter "fake" data. 2. Create three text fields (txtDay, txtMonth, txtYear). This would allow the use of "Null" for an unknown year (more correct "relationally", I *think*...). But it would be more complicated to do age calculations and sorting and the like, and would be more challenging to implement (Another supposition, since I haven't tried it...) Suggestions? Thanks! Fred Boer P.S. Let's imagine that forcing compliance would break a business rule... :) Fred If you don't have the year, how will you do "age calculation"? I suppose one approach might be to allow input (a text field, not a date/time field) of day and month, then use IsDate() to test. If the input is a date, proceed with age calculation...  SignatureRegards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ > Hi! > [quoted text clipped - 21 lines] > P.S. Let's imagine that forcing compliance would break a business rule... :) Based on an exchange in another thread, I believe that when Fred knows the person's birthday, but not his/her actual birthdate, he'll enter 1900-05-04 as DOB. That way, at least he'll be able to retrieve the records of all contacts with a birthday on a specific date (although he won't be able to calculate their age). He's obviously counting on the fact that he won't be dealing with too many 107 year olds! He uses IIf when calculating the age: IIf(Year([DOB]) = 1900, 0, <standard age calculation>) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Fred > [quoted text clipped - 23 lines] >> sorting and the like, and would be more challenging to implement (Another >> supposition, since I haven't tried it...) Hi Jeff and Doug: Jeff, my fault and sorry I wasn't more clear, but the age calculation isn't the key here. It *is* calculated and displayed on a form, but the control that displays the age has an expression to filter out unknown birth years and display a blank. The main point of the this process is to create a "birthday" reminder/tickler process, and also so that a report listing birthdays by day and month (not age or year of birth) can be generated. Yes, Doug, that's right, you saw that in the earlier thread. I'm trying to help Sue, and I wasn't sure if I was giving her good advice with this issue - hence this repost. So is a "dummy" year an ok approach? Thanks very much to both of you! Fred > Based on an exchange in another thread, I believe that when Fred knows the > person's birthday, but not his/her actual birthdate, he'll enter [quoted text clipped - 38 lines] >>> (Another >>> supposition, since I haven't tried it...) My only concern is that it IS still possible for someone to have been born in 1900. As well, 1900 wasn't a leap year, so you would be unable to store 1900-02-29. To avoid both of these issues, why not use 104 as the year, or 9996? (unless you're afraid this will lead to a Y9996 crisis <g>) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Hi Jeff and Doug: > [quoted text clipped - 59 lines] >>>> (Another >>>> supposition, since I haven't tried it...) I'm intending to be around then, aren't you? <g> Thanks, Doug! I never thought about the leap year issue... Fred > My only concern is that it IS still possible for someone to have been born > in 1900. [quoted text clipped - 4 lines] > To avoid both of these issues, why not use 104 as the year, or 9996? > (unless you're afraid this will lead to a Y9996 crisis <g>) I'm with Fred - the leap year issue never crossed my mind. Out of curiosity, was there a special reason why you selected "104" & "9996"? Thanks, all of you, for your input! > My only concern is that it IS still possible for someone to have been born > in 1900. [quoted text clipped - 68 lines] >>>>> (Another >>>>> supposition, since I haven't tried it...) Hi Sue! I think I can answer that. It's probably in the Help system somewhere, but I figured it out by experiment... If you enter 01/01/99 as a date, Access changes it to 01/01/1999. If you enter 01/01/100, Access accepts it. So 104 would be the first leap year acceptable to Access. Likewise, Access can accept 01/01/9999, but draws the line at 01/01/10000 - (not surprisingly!) And so... 9996 would be the last leap year Access could recognize. Fred P.S. Working from a DIAL-UP connection!! (My high speed is down due to faulty phone line!! I'm getting the shakes...) <g> > I'm with Fred - the leap year issue never crossed my mind. > Out of curiosity, was there a special reason why you selected "104" & [quoted text clipped - 74 lines] >>>>>> (Another >>>>>> supposition, since I haven't tried it...) That's absolutely correct, Fred. Access Date fields can hold 01/01/100 to 12/31/9999. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Hi Sue! > [quoted text clipped - 92 lines] >>>>>>> (Another >>>>>>> supposition, since I haven't tried it...) Thanks, Fred et al. Makes sense. 104 it is! I may meet a 107-year-old born on 2/29, but I darn sure don't expect to meet someone who's 1903 years old! Good luck getting back to high-speed, Fred. > I'm with Fred - the leap year issue never crossed my mind. > Out of curiosity, was there a special reason why you selected "104" & [quoted text clipped - 74 lines] >>>>>> (Another >>>>>> supposition, since I haven't tried it...) >I may meet a 107-year-old born on 2/29, but I darn sure don't >expect to meet someone who's 1903 years old! Hrumph. My beard was grey already in 104. And the Emperor graciously let me manage his library... John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.