MS Access 2007 Form

Posted on 2012-08-14
Last Modified: 2012-08-15
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.
Question by:Reyesrj
    LVL 61

    Assisted Solution

    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.
    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    <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


    Author Comment

    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.
    LVL 48

    Accepted Solution

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

    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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.

    Author Closing Comment

    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!!!
    LVL 48

    Expert Comment

    by:Gustav Brock
    Thanks. You are welcome!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now