Solved

MS Access Validate date format

Posted on 2007-12-06
12
4,298 Views
Last Modified: 2013-11-28
This is my third post for this topic. Think I'm almost there.
For those that have helped me get this far, thank you.

I need to add / modify the code below to check for international date format and prompt the user to reenter the data in the format mm/dd/yyyy

For example if the user enters 25/1/07 (International format dd/m/yy)  the value that gets stored in my table is 1/7/2025

I tried various attempts at checking to make sure the first two digits were less than 12 with no luck.

Assistance is greatly appreciated.
Private Sub Txt_Date_Opened_BeforeUpdate(Cancel As Integer)
 
'Stop
 
If Not IsDate(Nz(Me.Txt_Date_Opened, "foo")) Then
   MsgBox "Please enter a valid date in the format mm/dd/yyyy"
   Cancel = True
   DoCmd.CancelEvent
Else: Me.Txt_Dt_Opened_Bound = Me.Txt_Date_Opened
End If
 
End Sub

Open in new window

0
Comment
Question by:TylerDerden
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 20423158
That's not going to be possible if the user enters 1/2/07, as it could be January 2nd or February 1st.

DatePart("d", SomeDay)
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 20423166
Enclose Me.txt_Date_Opened in a format statement i.e. Format(Me.Txt_Date_Opened, "d/mm/yyyy").

This will change any valid date into this format
0
 
LVL 75
ID: 20423195
"the value that gets stored in my table is 1/7/2025"

Actually, no matter what the *format* - dates are stored as double precision numbers, with the date part to the left of the decimal point and the time value to the right of the number.  So really ... it's up to you to format the date as required - independent of how it's actually stored.

Also, checking for Not IsDate should be a separate validation test, not specifically related to the format entered.

So, the user s/b able to enter the date in whatever format ... and then in the AfterUpdate, you can reformat the date to whatever style you need.

mx
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 8

Accepted Solution

by:
dds110 earned 250 total points
ID: 20423365
By far not an elegant solution, but, you could provide the users with three seperate textboxes, one each for day, month, year and then concatenate them later.  Even better, you could provide them with a calendar to click on.

Just my 2 cents.
0
 

Author Comment

by:TylerDerden
ID: 20423733
Ah yes, the ActiveX Control Microsoft Date and Time Picke Cotrol 6.0.
Works perfect. I forgot about that one.
Hazards of doing this less than part time. lol.

Thank you every1 for your help.
0
 
LVL 75
ID: 20423784
How does the date picker control guarantee you displaying the the correct format ?

mx
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 20423805
Badly-worded question.  Bah.
0
 

Author Comment

by:TylerDerden
ID: 20423846
Its the data entry I was trying to control.
So many different ways to enter the same date:

In the table the field property format is set to Short Date.

Seems to work. The current records are displayed as mm/dd/yyyy
and the new records go in the table with the same format mm/dd/yyyy

No chance for the user to enter some strange format that gets past data validation and store an unintended date.
0
 
LVL 75
ID: 20423856
Also, from a User interface standpoint ... forcing the user to always use a date picker will slow down data entry.  Wherein a cool feature, I guarantee you that a typical person can type 12-5  (four characters) ... resulting in 12-5-2007 way faster than picking the date from a date picker.

mx
0
 
LVL 75
ID: 20423881
"eems to work. The current records are displayed as mm/dd/yyyy
and the new records go in the table with the same format mm/dd/yyyy"

Again ... how a date is formatted at data entry time has zero bearing on how Access actually stores the date. I'm not trying to take anything away from the solution here ... just trying to point out some things that may not be obvious.  IF ... I forces users to only use a date picker to enter dates ... I would be shot, lol.

mx
0
 

Author Comment

by:TylerDerden
ID: 20423897
Yes, I agree with your comment. Since I'm a part-time-part-time Access Developer I went with the solution that I can create and maintian that gets the job done.
This is a low volume application. Around 30 - 40 entries a week so a small sacrifice is speed is worth what I gain in ease of development and maintenance on my part.

Trade offs. Always trade offs.

I do greatly appreciate everyones comments. Gives me a chance to learn various ways to solve a problem.
0
 
LVL 75
ID: 20423989
On the other hand ... I often have a icon/button next to a date text box such that it pops up a date picker !

mx
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

623 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