• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

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.
3 Solutions
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 CoachmanCommented:
<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

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.
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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"))

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!


Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now