Solved Validation Rule to ensure that an entered value is a date (1 Viewer)

[jumnhy]

Hello all,

I am trying to make sure that the entered value in my form is a date.

I've avoided using an input mask because I don't like the UI problems inherent with those--gotta enter dates with 4 OR 2 digit years, but not both, for example.

Is there a way to retain that sort of flexibility (eg, 1 or 2 digit entries for days and months, 2 or 4 digit entries for years) with a validation rule?

IsDate() is the function that would seem to be appropriate, but I don't know how to pass the value of the cell to it in the expression.

Thoughts?

 

[MajP]

You said Cell so is this Access or Excel. If an access form you validate in the before update event. Then you can cancel and undo the value.

TextBox.BeforeUpdate event (Access)

Office VBA reference topic

docs.microsoft.com

 

[theDBguy]

Hi. Data validation is typically done in the BeforeUpdate event. So, you could try something like:

If IsDate(Me.ControlName) Then
    'all good
Else
    Cancel=True
    Me.ControlName.Undo
    MsgBox "Please enter a valid date.", vbInformation, "Invalid"
End If

Edit: Oops, too slow...

 

[Isaac]

@jumnhy
Two thoughts that may or may not help.

1. If your form is bound, and the control (textbox) in question is already bound to a date column as the controlsource, Access pretty much handles that validation for you. Your users won't be able to type "text" and tab out of the textbox in the first place. When exiting the textbox, they'd get "the value you entered isn't valid for that field". So in this scenario, there's not much more you need to do.
2. If #1 is true, OR you set the textbox's format to Date, it will show a calendar datepicker (built in) anyway, in which case, your users will end up with a valid date in the first place.

HTH

 

[jumnhy]

You said Cell so is this Access or Excel. If an access form you validate in the before update event. Then you can cancel and undo the value.

@MajP , this is in fact Access, I referred to it as a cell because it's a single cell on a datasheet view of a form.

Is there no way to do this using the Validation Rule property of the control? I'm fine to add it as an event in the after update property if I need to, but the validation rule seemed to be the inbuilt way to do this.

 

[theDBguy]

Is there no way to do this using the Validation Rule property of the control? I'm fine to add it as an event in the after update property if I need to, but the validation rule seemed to be the inbuilt way to do this.

But as @Isaac said, if your field is a Date/Time data type, there is no need for a validation rule at all. Access will automatically reject any input that is not a valid date.

 

[jumnhy]

Hi @Isaac ,

As they say, every time you make something idiot proof, they invent a better idiot...

I came across this because I have a custom module pulling in the date entered in this field to calculate the number of business days elapsed from the date of receiving to the date of entry to our inventory. Was getting a weird overflow error--turned out one of the end-users at the receiving dock entered the date "2/24/202". This entry does get accepted when entered, though I'd thought it wouldn't be. This was causing the overflow error in the custom VB.

Weirdly, mm/dd/yyy doesn't throw an error, though mm/dd/yy will auto insert [19]XX or 20[XX], but apparently some of the date functions are build to work with dates from the 3rd century?

Anyhow, my original plan to use IsDate wouldn't work here anyway-- IsDate(#2/2/202#) evaluates true.

Like @theDBguy and @MajP suggested, it seems throwing in the validation elsewhere is the solution. Either forcing a yy OR yyyy format in the "AfterUpdate" event, or doing validation within the module instead.

Thank you to everyone for your help, folks.

 

[Isaac]

Understood, then you probably need something to first check if it's a date, then check if it's in the requisite range.
When I think of how early Access allows dates to be (i.e., the year 202), I imagine maybe the first creators of Access were thinking it'd be used to record archeology finds.

 

[Pat Hartman]

2/24/202 is a valid date. So using IsDate() is going to return true. You have to define what you consider to be valid. In this case I've assumed that the date is required and cannot be more than four days in the past. Put your validation into the FORM's BeforeUpdate event.

If IsDate(Me.SaleDT) Then
    MsgBox "Sale DT is required.", vbOKOnly
    Cancel = True
    Me.SaleDT.SetFocus
    Exit sub
    Else
        if Me.SaleDT >= Date() -4 Then
        Else
            Msgbox ("Sale DT must be less than four days ago."), vbOKOnly
               Cancel = True
            Me.SaleDT
            Exit Sub
        End If
    End If

End If

Validation belongs in the BeforeUpdate event, NOT the AfterUpdate event because the AfterUpdate event cannot be cancelled and you need to cancel the save if the validation fails. That is how you keep bad data from being saved. Doing validation in the Form' BeforeUpdate event rather than in the control's event means you only have to do it in one place. If the field is required, checking for a valid date in the control's BeforeUpdate event doesn't help at all since that event will not fire if the user simply tabs or clicks past it without typing anything. Validating in the AfterUpdate event is akin to closing the barn door after the horses have escaped.

PS, relational databases do NOT have cells. They have tables and rows and fields. Forms have controls if you want to reference a "cell" on a form.

 

[MajP]

You can make some centralized validation functions.

Public Function IsValidFutureDate(dtmDate As Variant, Optional MaxYearsInFuture As Integer = 5) As Boolean
  'Lets assume normally you schedule within 5 years
  If IsDate(dtmDate) Then
     If dtmDate >= Date And (dtmDate <= Date + (365 * MaxYearsInFuture)) Then IsValidFutureDate = True
  End If
End Function
Public Function IsValidDateInRange(dtmDate As Variant, Optional MaxYears As Integer = 5) As Boolean
  'Lets assume normally you schedule within 5 years
  If IsDate(dtmDate) Then
     If dtmDate >= Date - 365 * MaxYears And (dtmDate <= Date + (365 * MaxYears)) Then IsValidDateInRange = True
  End If
End Function

You could set hard range dates or other versions of these.

 

[jumnhy]

Thanks for the elucidating response, Pat! You've been helping me all day...

And yes, while I get that 2/24/202 is a valid date, Microsoft makes some other assumptions in their in-built validation, like assuming that 2-digit years are referring to the 20 or 21st century. As a result, I was surprised they took 202. Technically valid, sure, internally consistent with other already-included features designed to enhance end utility ... No.

Re: order of operations for validation: so, use BeforeUpdate (because that fires before the data gets saved, ie, while the horses are in the barn). If I put the validation on the control instead, wouldn't that save it from running the validation code every time anything on the form is entered?

Thanks again for your help. Oh, and I didn't realize cell is specific to Excel and taboo (?) in Access. Seems like a bit of a sore spot?

 

[MajP]

Re: order of operations for validation: so, use BeforeUpdate (because that fires before the data gets saved, ie, while the horses are in the barn). If I put the validation on the control instead, wouldn't that save it from running the validation code every time anything on the form is entered?

If you put it on the control before update it will happen when you try to exit the control. If you put it at the form it happens when you try to move to a new record.
The form event can ensure it happens. I can think of cases (like update through code, or import) where you could get bad data but you never really fire the control before update. However, then you only get the check after you have done other things and now trying to leave the record.
It is nice to do in both places.
1. The control event will alert immediately after leaving the control
2. The form event will catch the exception.

Make a centralized check and you can call from both events.

 

[Pat Hartman]

Think of the form's BeforeUpdate event as the flapper at the bottom of a funnel. It is the LAST event that runs before a record gets saved. And nothing can bypass it regardless of who or what caused the save to happen. It only runs once and is immediately followed by the AfterUpdate event assuming the save was successful. Modifying the record in the Form's AfterUpdate event puts the code into an infinite loop. Current versions of Access recognize the loop when the call stack becomes excessively deep and gracefully exits. Earlier versions simply locked the computer and the screen flickered. If the save was successful, the dirty flag would be turned off and therefore, the event would not run again unless something or someone dirtied the record again. If you cancel the event as in the code I posted, the dirty flag is left set so the user either has to fix the problem or he has to use esc twice to back out the update before he can gracefully exit the form.

99.9% of all validation belongs in the Form's BeforeUpdate event or in procedures called by the form's BeforeUpdate event. You can use each control's BeforeUpdate event for some of it but you cannot do all validation in Control events. So, my standard is to just use the form's BeforeUpdate event and not worry about ever using the control's BeforeUpdate event. If you use procedures such as MaJP suggested, you can use them in the Form's BeforeUpdate event but you STILL have to cancel the event to prevent the record from being saved and you can't do it in the function because functions such as were suggested need to be in standard modules in order to be used by multiple forms.

I would use the control's BeforeUpdate event if I wanted to stop the user at that point if the entry was invalid. For example, if the employee record required SSN and SSN had to be unique, I would validate the SSN for uniqueness in the SSN control's BeforeUpdate event so I could avoid having the user waste his time entering all the other data if I wasn't going to let him save the record anyway. But, even in that case, validation is required in the Form's BeforeUpdate event because if SSN is required, then I can't validate for "required" in a control event because the control level events only fire if the control is entered and in the case of the BeforeUpdate event if the user actually typed something. So, the ONLY place I can reliably force a field to be not null is the Form's BeforeUpdate event. You have a similar problem if you have validation rules that involve multiple fields. For example, it makes no sense for TerminationDT to be < HireDT but you can't validate that in the control events because you can never be certain when to do it. If you do it in HireDT, TermiationDT should be null so you can't do it there. If you do it in TerminationDT, what happens if HireDT is empty for some reason? If you just give a message and leave TerminationDT, what if the user doesn't go back and fix the HireDT? The safe route would be to erase the user's entry in TerminationDT but that isn't very user friendly.

One other trick you need to know is that if a user types something in a control and then backspaces, the control stays dirty and it is not null so IsNull() will return false. There are two ways to check for both Null and a ZLS in the same expression:

1. If Len(Trim(Me.SomeField))> 0 '''''' the field is not null and not ZLS
2. If Me.SomeField & "" <> "" ''''' the field is not null and not ZLS

I use the second because old habits die hard and functions, especially two nested take infinitesimally more time to execute than a concatenation operation. It probably makes no difference in the greater scheme of things but I spent too many years creating CICS transactions used by thousands of people simultaneously and milliseconds count in that kind of environment so I always code the least expensive instruction when there are options.

You will find many people suggesting other events. They all require extra code in multiple events and there is still a likelihood that they missed something. I can almost always break these apps. And if I can break them, so can the user. I once removed 5,000+ lines of validation code from an app. The programmer did not know what I have just told you. He had code in five control level events of every single control and the code was validating not only the control it was in but all "previous" controls in the tab order and still bad data was being saved. Why? You can display all the error messages you want but if you don't close that pesky funnel at the end of the Form's BeforeUpdate event, the bad data WILL be saved unless it violates some RI rule and the data base engine prevents the save.

Do NOT put validation in multiple places. You just make more work for yourself and you run the risk of modifying one procedure and not the other. It also confuses people who have to maintain the db after you and when you do stuff like this to them that can trap them into doing the wrong thing, they will curse you and your offspring to eternity.

 

[MajP]

Oh, and I didn't realize cell is specific to Excel and taboo (?

Not taboo, but people will ask both Access and Excel here. I often give a long explanation in Access only to find the OP was asking about Excel and had no idea what I was saying. Validating a Cell in excel would be very different.

However in Access to avoid confusion (Fields, Records, and Controls)
Excel (Columns, Rows, Cells)

 

[jumnhy]

Think of the form's BeforeUpdate event as the flapper at the bottom of a funnel. It is the LAST event that runs before a record gets saved. And nothing can bypass it regardless of who or what caused the save to happen. It only runs once and is immediately followed by the AfterUpdate event assuming the save was successful. Modifying the record in the Form's AfterUpdate event puts the code into an infinite loop. Current versions of Access recognize the loop when the call stack becomes excessively deep and gracefully exits. Earlier versions simply locked the computer and the screen flickered. If the save was successful, the dirty flag would be turned off and therefore, the event would not run again unless something or someone dirtied the record again. If you cancel the event as in the code I posted, the dirty flag is left set so the user either has to fix the problem or he has to use esc twice to back out the update before he can gracefully exit the form.

99.9% of all validation belongs in the Form's BeforeUpdate event or in procedures called by the form's BeforeUpdate event. You can use each control's BeforeUpdate event for some of it but you cannot do all validation in Control events. So, my standard is to just use the form's BeforeUpdate event and not worry about ever using the control's BeforeUpdate event. If you use procedures such as MaJP suggested, you can use them in the Form's BeforeUpdate event but you STILL have to cancel the event to prevent the record from being saved and you can't do it in the function because functions such as were suggested need to be in standard modules in order to be used by multiple forms.

I would use the control's BeforeUpdate event if I wanted to stop the user at that point if the entry was invalid. For example, if the employee record required SSN and SSN had to be unique, I would validate the SSN for uniqueness in the SSN control's BeforeUpdate event so I could avoid having the user waste his time entering all the other data if I wasn't going to let him save the record anyway. But, even in that case, validation is required in the Form's BeforeUpdate event because if SSN is required, then I can't validate for "required" in a control event because the control level events only fire if the control is entered and in the case of the BeforeUpdate event if the user actually typed something. So, the ONLY place I can reliably force a field to be not null is the Form's BeforeUpdate event. You have a similar problem if you have validation rules that involve multiple fields. For example, it makes no sense for TerminationDT to be < HireDT but you can't validate that in the control events because you can never be certain when to do it. If you do it in HireDT, TermiationDT should be null so you can't do it there. If you do it in TerminationDT, what happens if HireDT is empty for some reason? If you just give a message and leave TerminationDT, what if the user doesn't go back and fix the HireDT? The safe route would be to erase the user's entry in TerminationDT but that isn't very user friendly.

One other trick you need to know is that if a user types something in a control and then backspaces, the control stays dirty and it is not null so IsNull() will return false. There are two ways to check for both Null and a ZLS in the same expression:

1. If Len(Trim(Me.SomeField))> 0 '''''' the field is not null and not ZLS
2. If Me.SomeField & "" <> "" ''''' the field is not null and not ZLS

I use the second because old habits die hard and functions, especially two nested take infinitesimally more time to execute than a concatenation operation. It probably makes no difference in the greater scheme of things but I spent too many years creating CICS transactions used by thousands of people simultaneously and milliseconds count in that kind of environment so I always code the least expensive instruction when there are options.

You will find many people suggesting other events. They all require extra code in multiple events and there is still a likelihood that they missed something. I can almost always break these apps. And if I can break them, so can the user. I once removed 5,000+ lines of validation code from an app. The programmer did not know what I have just told you. He had code in five control level events of every single control and the code was validating not only the control it was in but all "previous" controls in the tab order and still bad data was being saved. Why? You can display all the error messages you want but if you don't close that pesky funnel at the end of the Form's BeforeUpdate event, the bad data WILL be saved unless it violates some RI rule and the data base engine prevents the save.

Do NOT put validation in multiple places. You just make more work for yourself and you run the risk of modifying one procedure and not the other. It also confuses people who have to maintain the db after you and when you do stuff like this to them that can trap them into doing the wrong thing, they will curse you and your offspring to eternity.

A million thanks, Pat, once again! Beautiful explanation of the advantages of validation in the BeforeUpdate event of the form.

My issue now seems to be that when I prompt the user to enter a complete date (and you might have gotten at this when you reference how a control can be "dirtied" ... If they reenter it properly, the warning still fires again after the re-entry/correction. Can't figure out for the life of me why. Thoughts?

Basically, incorrect entry --> tab through to next record, BeforeUpdate triggers after pressing tab, pops up w/ gentle reminder to fill in the $%&@ date correctly, focus goes back to the right control, enter the date right, tab over again, and then the warning still pops up again.

Something to do with the "dirtying" of the control you mention? How do I get out of this loop?

Update: I'm an idiot. I mindlessly copied Pat's code--but it really needed to be "Not IsDate(xxx)" rather than IsDate(xxx). The error condition was simply firing all the time... because the date was always a date.