validation rule and easy dates

[AnOddExperiment]

Hello again. Just trying to clean some things up in my forms to make them easier to use.

One thing I need is a validation rule on my form such that it accepts no more than 10 characters, but fewer is OK. In the help files and on a website I found examples for

Like "??????????"

but that requires exactly 10 characters, and I just need 10 or less.

The other thing is I was wanting to know if there is an easy way (via ready to go Access features or VBA) if you can autoformat a date into short date format. i.e. I want to be able to enter 3308 and get 03/03/2008 as well as 122907 and get 12/29/2007. An input mask seems the obvious choice but use of something like

99/99/00;;_

forces me to enter 030308 to get 03/03/2008. Any ideas on these two items?

 

[missinglinq]

If your only concern is limiting the field to a maximum of 10 characters, the simplest thing to do is, in Design View for your table, set the Field Size to 10!

And no, there is really no way, easy or otherwise, for Access to guess that 3308 is supposed to be 3/3/2008. Aside from the problem of logic, if you're entering/storing a date, you should be using a date field. Entering 3308 or the like would throw an error.

 

[georgedwilkinson]

What about:
Jan 1, 2011 = 1111
Jan 11, 2001 = 1111
Nov 20, 2008 = 112008
Jan 1, 2008 = 112008

Seems logically impossible to do what you want to do. But a locale specific date mask will help.

 

[missinglinq]

Exactly right, George! That's what I meant by "Aside from the problem of logic!" The OPs really asking Access to read the end user's mind! There is a limit to how "easy" you can make a user interface.

 

[gemma-the-husky]

its only a matter of keying in 1/1/08 instead 1108 anyway - to avoid confusing access

or you could allow 6 figure dates, say 010108

------
all this stuff is a bit 3GL programming though isnt it - input a date as a number, and mask it to display what you want.

Access doesnt hold dates as numbers - it stores the total days (and fractional days for time) since a date in 1899. Try this to see.

Sub showme()
Dim mydate As Date
mydate = 0
MsgBox (Format(mydate, "short date"))
End Sub

 

[missinglinq]

Access doesnt hold dates as numbers - it stores the total days (and fractional days for time) since a date in 1899. Try this to see.

Actually, that statement is both right and wrong! It does store the dates internally as numbers, the numbers being the the days/partial days since 12/30/1899.