Date validation happening before BeforeUpdate

[MarkGibson]

I am trying to validate dates in a manner that is easier for the user than the input masks. But when I try to use an ISDATE(me!txtDate) in the BeforeUpdate event of my form, Access 2000 rejects the bad data before it gets to the BeforeUpdate. It seems to be seeing that the data is not a date (which is how it is identified in the table definition), and issuing the standard error, "The value you added isn't valid for this field."

Can I get around this? How do you others validate dates in a way that is the most friendly for your users?

Thanks a lot,

Mark

 

[MarkGibson]

Alternately, is there a way to issue my own message rather than the input mask error message that says that &quot;The value entered does not match a mask of type #9\->L<LL\-00;0&quot;.

That is not the most helpful message to offer!

Mark

 

[johncb]

You could try putting the validation on the ON EXIT event for the date field.

John

 

[WildHare]

Is there any particular reason you are forcing your users to input dates in a specific format? If you have a field typed as DATE, any valid date input should be allowable, even if you want to DISPLAY that date in some other form, e.g. &quot;4/3/02&quot;, &quot;04/03/2002&quot;, &quot;April 3&quot;, &quot;3 April&quot; etc etc etc are all valid date inputs. It doesnt matter HOW you want to display the date later on. I find that most users get frustrated if they find they have to enter dates with a specific format (e.g. MASK).

You can then just edit the VALIDATION TEXT for the field and display a more appropriate message.



Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[MarkGibson]

John: thanks for the idea, but like the BeforeUpdate, it is triggered AFTER the table validates that this is not in proper date format.

Jim: I agree entirely, but am having troubling formulating the code to ensure that it is a date, and thus trigger the validation text if it is not. For example, ISDATE() is rejected (not enough arguments), ISDATE becomes &quot;isdate&quot;, etc. And the table's internal validation doesn't trigger the validation text (neither the validation text of the table nor of the text box on the form)....

I'm sure there is an elegant way to do this!

Mark

 

[WildHare]

is this field typed as a DATE in the table design? That's all you need. If you enter a NON-VALID calendar date in any DATE-typed textbox, it will set of the type validation before ANYTHING else. Then just display your VALIDATION TEXT.

I've been using Access and relational db programs for almost 22 years, and I've NEVER had to use an &quot;IsDate?&quot; function to do the kind of thing you are talking about. If the data is typed correctly in the first place, you don't need that stuff.





Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[MarkGibson]

The field is set to Date in the table, and that seems to be where the problem is: it rejects the data if it is not a date with a generic message (i.e. &quot;The value you entered is not valid for this field&quot rather than the custom message that I would like to have appear, which might be (&quot;You must enter a date in this field.&quot How do you go about presenting a custom message in this case?

Mark

 

[WildHare]

Doesn't a custom message appear if you enter it in the VALIDATION TEXT in table design?

It might be that you need to actually enter a &quot;validation rule&quot; there to kick it off - so try a very general one, such as

> #1/01/1901#

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[MarkGibson]

I just checked again, and it doesn't, even with the validation rule you suggested.

I appreciate your helping me figure this out!

Mark

 

[WildHare]

Well, we haven't figured it out yet. Just so I'm on the same page, you have a DATE field in your table, and no matter what we seem to do, the default Access error message appears if you type in something like &quot;October 48th&quot;, overriding any custom error message? That seems bizarre, but not necessarily out of the ordinary in MSLand...

Have you tried the ON CHANGE event in your text box?

Can you turn off error messages (SetWarnings FALSE) and handle it yourself?

Can you ignore Errors (on Error Resume Next) and then programmatically catch it?

Tossing straws here..time for lunch..back soon

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[MarkGibson]

I'm afraid all of your &quot;staws&quot; don't work or don't apply; this triggers after the On Change, but I don't think there is a reasonable way to know that we have come to the end of the typing in this field (maybe watch for tabs, Enter, Return, Arrows....)

Thanks for the attempts.

 

[WildHare]

Mark - this was suggested by one of the members of the Access forum I host - it looks like it might do the trick for you:

-----------------------------------------
Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 2113 Then
Me.Undo
MsgBox &quot;The value you have entered is not a valid date!&quot;
Response = acDataErrContinue
Else
'If another error is applied then show the standard error message
Response = acDataErrDisplay
End If

End Sub
----------------------------------------- Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[MarkGibson]

That is a very interesting approach, Jim. I hadn't noticed that we had that sort of error-trapping ability. I will just have to add a flag to specify that we have just entered a date field (so the standard errors come out elsewhere, if they are needed), and I am set.

Thanks a lot to you, and to your forum member!

Mark