validate input mask proper date mm/dd/yyyy


I have an input mask on a DOB field as a date, currently they can enter date as dd/mm/yyyy, but I want it as a standard format

How can I check to be sure this is correct?
Ernest GroggSecurity Management InfoSecAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure what you mean by "standard format", so you'd have to provide a definition for that.

An Input Mask will require the user to enter data that conforms to the mask, so ... if that mask forces the desired format, then you should be okay.
Ernest GroggSecurity Management InfoSecAuthor Commented: the mask is

Which apparently allows: 15/05/1980

But I want that to be: 05/15/1980

I want to make sure it is this date way.  Field is text may be I should change to a date field type?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, you should be entering Dates into a Date Field.

Your input mask will force the user to enter things correctly, but be aware that you'll also have to take into account the Windows settings of the user. For example, my machine uses US-style date (i.e. mm-dd-yyyy), so I'd enter that as 05-15-2015. If your machine is UK-based, you'd enter it as "dd-mm-yyyy", so a value of 05-15-2015 would not be valid.

But that really won't have an impact, since Access will be able to determine the various "date parts" from the data entered and the windows settings and will use those settings for calculations, queries, and so forth. So if I were to enter "05-15-2015" on my machine, Access know that I mean May 15th, 2015.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Personally, I never use an input mask for a date field.  For starters, using an input mask prevents the calendar from popping up.  The other problem is that it forces people to ALWAYS enter 8 digits.  When people click into the control rather than tabbing into it, they must consciously select the whole field before they can type or what they type could end up in the wrong place.  And finally, as you have discovered, it doesn't really do what you want anyway.  I like to be able to enter 3/2 for February 3rd, this year.  With an input mask, I must type 03022015.

For unbound controls, make sure the data type is defined as date time.  Access will never allow you to enter an invalid date in an unbound control with the data type defined as date time or in a control bound to a column with a date time data type.  Therefore, your editing requirements are limited to range checking and presence checking.  Range checking can go in the control's Before update event as long as it doesn't involve comparing to a different control.  So for example, you would not allow a DOB > today.  However if you are entering a date range, and you want to ensure that the start date is <= the end date, the best place to do that is the Form's BeforeUpdate event.  At that time BOTH the required dates should have been entered.  And the ONLY valid place to do "presence" checks is the Form's BeforeUpdate event.  You can't check for null values in a control level event because control level events only fire if the control gets the focus.  So, if the user never entered a DOB, you can't raise an error until the Form's BeforeUpdate event because nothing will trigger DOB control code to run.
Be aware also that while Access may play nice with various date formats, SQL Server is known to only enjoy US-style date (i.e. mm-dd-yyyy).  If English is the only language in use then dd-mmm-yyyy is by far the preferred format to remove ambiguity as far as I am concerned.  But if you have French, German, or Spanish on the go, well that goes out the door.

"standard format",
There is one, an ISO Standard, and it is yyyy-mm-dd, and if you do everything outside the Islamic world, it is the way to go.
If you need Hijiri dates, well then all bets are off.

Complex enough yet?
And we haven't even talk about the badness that occurs if you use Now() when you really want Date()

Personally, I never use an input mask
+10 to that @PatHartman.
Input masks are of the devil.  They make for very bad UX.
@Ernest Grogg: Validate your data before committing it.  It makes for much happier DATA and USERS
Gustav BrockCIOCommented:
There is no 100% method to catch this except user education.

However, you can use the BeforeUpdate event to catch most of these "reversed entry" errors:
Private Sub txtYourTextbox_BeforeUpdate(Cancel As Integer)
 ' Intercept inappropriate date strings.
  Dim ctlDate As TextBox
  Dim strFormat As String
  Set ctlDate = Me!txtWithCheck
  With ctlDate
    strFormat = .Format & vbNullString
    If Len(strFormat) = 0 Then
      ' Format property is missing. Use default format.
      strFormat = "Short Date"
    End If
    Cancel = Val(.Text) <> Val(Format(.Text, strFormat))
    If Cancel = True Then
      ' Undo to clear entry.
      ' Don't undo to leave input visible for the user.
    End If
  End With
  Set ctlDate = Nothing
End Sub

Open in new window

The trick is the use of Val. If you enter, say, 16/1/05 it goes like this:

    16 <> Val(Format(16, strFormat)
    16 <> Val(01/05/16)
    16 <> 1

and Cancel is set to True.

Ernest GroggSecurity Management InfoSecAuthor Commented:
Thanks to everyone for their input and help, very informative and taken....

Thanks I am on the right track now....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.