How can I accept partial data for a date

[emandel]

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?

[missinglinq]

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.

[emandel]

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.

[missinglinq]

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!

[emandel]

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?

[Killer42]

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.

[missinglinq]

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!

[NeoPa]

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.