[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS Access - VBA - Test Data Format

Posted on 2012-09-07
10
Medium Priority
?
420 Views
Last Modified: 2012-09-07
Is there a clean easy way to test that a date is formatted as mm/dd/YYYY and not m/d/yy or some other variation?

I need to make sure the format is MM/DD/YYYY
0
Comment
Question by:keschuster
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 38377175
If you need a date to be in that format, you should use the format function:

mydate = Format([DateField],"mm/dd/yyyy")

Flyster
0
 

Author Comment

by:keschuster
ID: 38377179
i want a true/false test that it IS in that format
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38377324
Keschuster,

Dates are all stored as double precision numbers  where the integer portion is the number of days since Dec 30, 1899.  The decimal portion is the Percentage of an elapsed day (6AM = .25, Noon = .5, ...).

Depending on your regional settings and formatting, that value can be displayed in a wide variety of formats.  If you want to force the display of that data into a particular format, you can use the Format() function or the Format property associated with a Query column or a forms control.

I am not aware of a way to test whether the field is displayed as MM/DD/YYYY as opposed to some other format.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:keschuster
ID: 38377329
my dates are stored as text
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38377332
Try
[DateFIeld] Like "??/??/????"
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38377343
Sorry, not possible.

Dates like 04/05/2012 could be either of May or April.
You have to know the format to parse such a stringe correctly.

/gustav
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38377376
"my dates are stored as text"

That would be a problem, unless you control the format that is used for data entry (or use a calendar control).
0
 
LVL 22

Accepted Solution

by:
Flyster earned 2000 total points
ID: 38377437
Try something like this:

CheckFormat: IIf([TextDate]=Format([TextDate],"mm/dd/yyyy"),"True","False")
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38377470
I believe that if [TextDate] is a string that looks like "30/5/12"  Then using the format function to transform that to "mm/dd/yyyy" format will result in 5/12/1930.

But I still don't know how you intend to identify whether "5/7/12" is supposed to represent May 7th or July 5th.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38377505
That "solution" doesn't test anything.

/gustav
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…
Suggested Courses

872 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