How to trap date field entry error (1 Viewer)

[lana]

Hi there,
I need to trap the date field entry errors on my forms.
The date format is mm/dd/yyyy
If it is entered as 08/20/2021, everything is OK, but if it is changed to 13/20/2021, I could not trap the error because the field value is still the old one (08/20/21).
I need to trap the error and show a message that the month can not be more than 12, or ....

Any ideas?

Thanks so much.

 

[Gasman]

Just set the format to Short Date and let Access handle it?

 

[arnelgp]

can you use the BeforeUpdate event of the date textbox?

private sub dte_BeforeUpdate(Cancel As Integer)
dim sDate As String
sDate = Me!dte & ""
Cancel = (IsDate(sDate)=False)
If Cancel Then
Msgbox date must be in the format 'm/d/yyyy'"
End If
end sub

 

[lana]

Just set the format to Short Date and let Access handle it?

I need to show my own messages, If possible!

can you use the BeforeUpdate event of the date textbox?

Before the beforeUpdate event fires, the error message appears.

I trap the error in the OnError event but the value of the text box is still the old one because it is not updated.

Thanks

 

[arnelgp]

after the message appears, you Undo the control.

private sub dte_BeforeUpdate(Cancel As Integer)
dim sDate As String
sDate = Me!dte & ""
Cancel = (IsDate(sDate)=False)
If Cancel Then
Msgbox date must be in the format 'm/d/yyyy'"
Me!dte.Undo
End If
end sub

 

[lana]

It does not work.
When the date is entered incorrectly, the _onError event fires, the _BeforeUpdate event does not fire.
When I look at the field value in the OnError event, it's null, or the old value, not the incorrect entry.
Thanks again for your time.

 

[The_Doc_Man]

In that context, look at the control-name.text property rather than the control-name.value, because that error occurred before the update went through. I.e. if you have a form On_Error event, it fires before any update-related event. And the .Value is not updated in that case.

 

[Cotswold]

Hi Lana, I think that you may need to change the Short Date Format in Windows. Access should then follow that.

Since the year 2000 it has been easier to use dates with a four digit year. I know it wastes screen space but it
avoids having to tell clients how, or worse, explaining step by step about making the change in Windows.
Before that everyone used the two digit year. Then from 2000 with a two digit year it wouldn't be possible to
know the difference between 1910 and 2010. As far as I know Access will store the date as YYYY regardless
of the Windows setting, but you may want to test it.

Some software from major companies only allow a four digit year, so it is unclear if changing the short date
in Windows to MMDDYYYY would cause issues in other software.

 

[theDBguy]

Does this mean you're not using the date picker? Just wondering...

 

[lana]

Thank you all.
control-name.text worked like a charm.

Many thanks again.

 

[Pat Hartman]

Access is perfectly capable of only allowing valid dates. Your validation code should concentrate on other types of typos or logic errors. These are validations that Access cannot do for you because they are valid dates. They just don't make sense in context. For example entering 221 as a year is a common typo. Your code needs to check for dates that don't make sense in context. For example, future dates as a DOB or a date that makes a job applicant older than retirement age. It might be valid but probably not. If you use an edit like this, if the applicant is > 65, you might ask if the date should be accepted and then do a further check to see if the applicant is > 80 years old. at some point, you might decide to not allow the entry. Same for if the applicant is < 16.