By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

How can I accept partial data for a date

emandel
P: 65
I have a field in my "members" table that asks for birthday
I don't want it to be so rigid that it will only take full information or nothing. In other words how can I get it to accept only a year if that is all I have or only the month and day without the year. is this possible?
Dec 27 '06 #1
Share this Question
Share on Google+
7 Replies


missinglinq
Expert 2.5K+
P: 3,532
The only way you can do this, I would think, would be to use a field of a text datatype, rather than a date datatype. You wouldn't be able to use any of Access' native date functions, but you couldn't use them anyway with only partial dates.
Dec 27 '06 #2

emandel
P: 65
I am relying on access to use the data as a date so for example I need to know who is under 40 years old but the birthday per se is not so important.
Dec 27 '06 #3

missinglinq
Expert 2.5K+
P: 3,532
Well, the simple fact is you can't tell how old someone is from the year of birth alone, you have to have the entire date!
Dec 27 '06 #4

emandel
P: 65
Yes but I could know that he is older than someone else in the db. I could seperate the birth year from the month and day and have it in a seperate field. but could I get access to recognize either fields as a date?
Dec 28 '06 #5

Expert 5K+
P: 8,434
Yes but I could know that he is older than someone else in the db. I could seperate the birth year from the month and day and have it in a seperate field. but could I get access to recognize either fields as a date?
One thing you could do is to store it as a numeric field in the form yyyymmdd. Any unknown portion is set to zero. Thus, if you only had a year, say 1978, the value would be 19780000. This may not be much use as a date, but would certainly work for comparison purposes.

If you need to be able to use date processing, what about storing two values? One actual date (setting unknowns to 01, perhaps) to allow an approximate age to be calculated, and one "comparison date" as described above.

A coupe of examples:
A: The 1978-born person
Date for age calculation: 1st Jan, 1978
Number for comparison: 19780000

B: Someone whose full birth date is known
Date for age calculation: 20th May, 1952
Number for comparison: 19520520

You might get away with using a single date field, but would probably need some way to determine which parts were real data and which made up.
Dec 28 '06 #6

missinglinq
Expert 2.5K+
P: 3,532
I don't want it to be so rigid that it will only take full information or nothing. In other words how can I get it (Access) to accept only a year if that is all I have or only the month and day without the year. is this possible?
I could seperate the birth year from the month and day and have it in a seperate field. but could I get access to recognize either fields as a date?
A DATE consists of a month, a day and a year. Neither Access nor any other computer language can recognize anything else as a date.

I am relying on access to use the data as a date so for example I need to know who is under 40 years old but the birthday per se is not so important.
The birthday is important! Without it you cannot tell if someone born in 1966 is 39 (born after 12/28) or 40 (born 12/28/66 or earlier.) You certainly can't tell how old someone is if all you have is a month and day!
Dec 28 '06 #7

NeoPa
Expert Mod 15k+
P: 31,602
In answer to your original question :
You could store a text field allowing them to enter what they like. You would have to check that it resolves to a date of some form before updating.
FYI entering a string of 'March 1985' would be ok as it would convert happily to 1 March 1985.
You need to remember that any comparisons on non-whole dates are unreliable and approximate at best.
Dec 28 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.