Solved

MS Access Validate date format

Posted on 2007-12-06
12
4,290 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

839 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