Link to home
Start Free TrialLog in
Avatar of GeneBat
GeneBatFlag for United States of America

asked on

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...
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>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
Avatar of GeneBat

ASKER

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!?
Avatar of GeneBat

ASKER

Also, Do I do stuff in the Before Update and After Update properties?
Avatar of twintai
twintai

You need to call the function in the After Update, Exit OR Lost Focus event.
SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GeneBat

ASKER

Let me try this code and get back to you (AW). I think you have it.
> 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
Avatar of GeneBat

ASKER

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.
Avatar of GeneBat

ASKER

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.
Avatar of GeneBat

ASKER

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?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
glad to be of assistance

AW