MS Access 2007 Form

Hi All

I am creating a MS Access 2007 database.  I’m hoping someone could help me with this.

I have a date of birth field.  If we are not able to acquire the date of birth then we are to enter 99/99/99.  Is there a way to allow only dates and 99/99/99 in to the date of birth field?

Thanks in advance.
Who is Participating?
Gustav BrockCIOCommented:
What a bad "another system".

Store your dates as dates (data type Date) and store unknown as Null.

Then, when exporting to that system, use a query which has this expression to create a text output for the date field:

TextDate: IIf(IsNull([YourDateField]), "99/99/99", Format([YourDateField], "mm\/dd\/yy"))

You can't enter that data in a date field.
I'd recommend allowing nulls instead and maybe a check box if "Could not be determined" is indeed important information.

Alternatively (less preferable ) use a formatted text field instead of a date.
Jeffrey CoachmanMIS LiasonCommented:
<If we are not able to acquire the date of birth then we are to enter 99/99/99.>
Yes, what is the reason why the date field cannot be just left blank?
(ultimately I agree with mbizup)

Alternatively (or perhaps even less preferable ) Enter 12/31/1899, or 1/1/1900

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

ReyesrjAuthor Commented:
Thanks All,
We are collecting data that will be extracted into excel and converted into a .dat file.  The .dat will be uploaded into another system and 99/99/99, in that system, represents unknown or not stated.  I know I could make the changes in excel but, I'm just thinking less work for me.  I really like mbizup suggestion to format the field.  I will close this question tomorrow just in case someone suggest something else.
Dale FyeCommented:
No points, please.

I agree with all of the above.  Leave the field blank (NULL) if it is unknown (after all, that is what a NULL represents).  Then use gustav's technique of formatting that NULL to "99/99/99" when you do your export to Excel.
ReyesrjAuthor Commented:
Yes I agree "another system" is bad.<br />I work for a local government and we collect date for the federal government.  With Access we could create databases on the fly and get the data to the feds quickly.  It’s the best we can do for now.<br /><br />cactus_data, I really like your suggestion.  This is very useful for other fields in my database.  The fed’s do not want blank data fields in their .dat file.  At the same time there won’t be a need to train users on what to enter if the data is not available.  Now I will not need to clean-up the data after running an excel query.  I’ll let Access do all the work for me.  You make my job enjoyable again!<br /><br />Thanks!!!
Gustav BrockCIOCommented:
Thanks. You are welcome!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.