Solved

MS Access Validate date format

Posted on 2007-12-06
12
4,287 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

11 Experts available now in Live!

Get 1:1 Help Now