Solved

MS Access Validate date format

Posted on 2007-12-06
12
4,297 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 65

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 65

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

738 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