Solved

MS Access Validate date format

Posted on 2007-12-06
12
4,288 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

21 Experts available now in Live!

Get 1:1 Help Now