We help IT Professionals succeed at work.

Date Check Routine

GeneBat
GeneBat asked
on
Medium Priority
738 Views
Last Modified: 2013-11-28
I  need to implement the DateCheck routine in form.  The routine is used for the StartDate and EndDate fields in the form.
I will need to save this function in a global module and use it in the before or after update events of these fields to tell the user that the dates they enter are incorrect. Ok to drop-thru with no message if the dates pass the test.

Here's the function that was passed to me to use...

Function DateCheck(PD As Variant, SD As Variant, ED As Variant) As Boolean
On Error GoTo DateCheck_ERR
 
Dim S, E, P As Date
DateCheck = False
 
If IsNull(PD) Or IsNull(SD) Or IsNull(ED) Then Exit Function
   P = CDate(PD)
   S = CDate(SD)
   E = CDate(ED)
 
   If ((PD <> SD) Or _
        (ED > FindEndDate(SD)) Or _
        (SD > ED)) Then
        DateCheck = False
         Else
      DateCheck = True
   End If
 

DateCheck_EXIT:
 Exit Function
 
DateCheck_ERR:
 Select Case Err
   Case 2501 '//  Docmd cancelled
    Resume DateCheck_EXIT
 
   Case Else
    '// on any type of error, exit function
    'Call ER(Err, Error, "basRP_TR.DateCheck", MH)
    Resume DateCheck_EXIT
 End Select
 
End Function

Sometimes I really don't understand these request because I'm a rookie...
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>I  need to implement the DateCheck routine in form.  
Okay.  Tell us exactly how you need to check dates, as the real ugly copy-pasting of code doesn't make it abundantly clear what you are asking us to help you with.
#1:  this line

Dim S, E, P As Date


does NOT do what you think it does.  It DOES NOT declare the variables S and E as Date - they are declared as variant her.  What you want is either:

Dim S As Date, E As Date, P As Date

or

Dim S As Date
Dim E As Date
Dim P As Date


what the code is doing is this:

First, if any of the parameters that are being passed into the function are NULL (that is they do not have a value of any kind), the function exits, and the value is False.

If all three have values, then they are converted into Dates, usinf the cDate function, which is built into Access.

The three dates are then validated:

   If ((PD <> SD) Or _
        (ED > FindEndDate(SD)) Or _
        (SD > ED)) Then
        DateCheck = False
         Else
      DateCheck = True
   End If
so that the Function will return False if
  1) PD and SD are not the same date, or
  2) ED is after the value returned by the function FindEndDate which is passed the value of the StartDate, or
  3) the StartDate (SD)  is after the EndDate(ED)

If all three of these tests are FALSE, then the Dates are Valid, and the Function returns True.

AW

Author

Commented:
So, if I had a Form with 3 textboxes:
PlanDate
StartDate
EndDate
As the fieldnames, how would I write the routine to check that? That is what is confusing me too!?

Author

Commented:
Also, Do I do stuff in the Before Update and After Update properties?

Commented:
You need to call the function in the After Update, Exit OR Lost Focus event.


If DateCheck(PlanDate.Text, StartDate.Text, EndDate.Text) Then
    ' Dates were Valid, so procede
Else
    MsgBox "Dates are invalid, Please try agaion", vbOkOnly
End If

AW

Author

Commented:
Let me try this code and get back to you (AW). I think you have it.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
> Do I do stuff in the Before Update and After Update properties?

You perform the check in the BeforeUpdate event:

  Cancel = Not DateCheck(PlanDate.Value, StartDate.Value, EndDate.Value)

  If Cancel = True Then
    MsgBox "This combination af dates cannot be used.", vbOkOnly, "Date Check"
  End If

Also if you like, as your function returns a Boolean, this can be reduced to:

  If Not (PD <> SD Or _
      ED > FindEndDate(SD) Or _
      SD > ED) Then
    DateCheck = True
  End If

/gustav

Author

Commented:
Thanks gustav let me add that code and see what happens. I'm also trying AW code snipped as well today. I'll let you know how it goes.

Author

Commented:
Question, can I add a validation rule to the textbox property of the control?  And a validation text saying something like "Plan date is blank." How do I do that? The code snippet is working. But just need to make sure that dates are there and not blank.

Author

Commented:
Can I do something like this in the Validation Rule of a property of a Textbox: <>0
and Validation Text can say "Please enter a date."

Is that doable?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Expand your function to return a error message:

Function DateCheck( _
  PD As Variant, _
  SD As Variant, _
  ED As Variant, _
  ByRef strError As String) As Boolean

Then make a series of checks:

  If IsNull(Me.Plandate.Value) Then
    strError = "Please enter a plan date."
  ElseIf PD <> SD Then
    strError = "Plan date should match start date."
  ElseIf ED > FindEndDate(SD) Then
    strError = "End date cannot be later than ..."
  ElseIf SD > ED Then
    strError = "Start date cannot be later than end date."
  Else
    DateCheck = True
  End If

Now, modify your BeforeEvent:

  Dim strError As String
  Cancel = Not DateCheck(PlanDate.Value, StartDate.Value, EndDate.Value, strError)

  If Cancel = True Then
    ' An error message is returned in strError.
    MsgBox strError, vbOkOnly, "Date Check"
  End If

/gustav
glad to be of assistance

AW

Explore More ContentExplore courses, solutions, and other research materials related to this topic.