Magnum Forcing YYYYMMDD - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Magnum Forcing YYYYMMDD

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-04-23 : 14:44:40

Gotta text box in a form, want a vaild date in this format to be entered (without overflow errors). I tried this in the data tab:

Input Mask: 00009999;0;_
Validation Rule: Val(Mid([Admit Dt],5,2))<13 And Val(Mid([Admit Dt],5,2))>0 And Val(Right([Admit Dt],2))<32 And Val(Right([Admit Dt],2))>0 And Val(Left([Admit Dt],4))>1999

No luck.

Tried this in the code:

Private Sub Admit_Dt_AfterUpdate()
Me.Admit_Dt = Format(Me.Admit_Dt, "yyyymmdd")
End Sub

No luck.

Any ideas what I can do here?

Thanks!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-23 : 15:01:42

It's been a while, is there an IsDate() function in Access? If that returns a 0/false, you know it's not a valid YYYYMMDD value.

If that doesn't do it, you might have to look at using an OnKeyPress event and have it validate each keystroke. If it would cause an invalid date, then you return false and it will cancel the key.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-04-23 : 15:16:25

Yes, I did this:

Private Sub Admit_Dt_AfterUpdate()
Me.Admit_Dt = IIf(IsDate(Me.Admit_Dt), Me.Admit_Dt, " ")
End Sub

And it worked!

.....But now I have to find a way to force the user to saty on this field until a valid entry is made!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 15:25:20

There should be an event for Exit, where you can change the Cancel parameter according to if the date is valid or not.

E 12°55'05.63"
N 56°04'39.26"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-23 : 15:25:40

Probably do the same on the BeforeUpdate event and cancel it if it's not valid. They won't be able to exit the control.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-04-23 : 15:40:33

Could you be a little more specific?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 15:46:10

The textbox where the user enters the date, has event.
Right click on the text box and chose Events.

And in the BeforeUpdate or Exit event, write

Cancel = NOT ISDATE([Admit dt])

E 12°55'05.63"
N 56°04'39.26"

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-04-23 : 15:57:06

But Admit_Dt is a String, not a real date. So it never clears.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 16:18:32

You are using an InputBox to populate the string?

E 12°55'05.63"
N 56°04'39.26"

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2009-04-23 : 16:22:43

Yes, this is a MS Access form, so the data is entered as a string.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 16:24:40

Dim sDate As String

sDate = 'abc'

While ISDATE(sDate) = 0
sdate = inputbox( ... )
Wend

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources