Date Check Routine

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...
GeneBatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
Arthur_WoodCommented:
#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
0
GeneBatAuthor 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!?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

GeneBatAuthor Commented:
Also, Do I do stuff in the Before Update and After Update properties?
0
twintaiCommented:
You need to call the function in the After Update, Exit OR Lost Focus event.
0
Arthur_WoodCommented:


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
0
GeneBatAuthor Commented:
Let me try this code and get back to you (AW). I think you have it.
0
Gustav BrockCIOCommented:
> 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
0
GeneBatAuthor 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.
0
GeneBatAuthor 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.
0
GeneBatAuthor 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?
0
Gustav BrockCIOCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Arthur_WoodCommented:
glad to be of assistance

AW
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.