GeneBat
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...
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...
#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
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
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!?
PlanDate
StartDate
EndDate
As the fieldnames, how would I write the routine to check that? That is what is confusing me too!?
ASKER
Also, Do I do stuff in the Before Update and After Update properties?
You need to call the function in the After Update, Exit OR Lost Focus event.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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.
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?
and Validation Text can say "Please enter a date."
Is that doable?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
glad to be of assistance
AW
AW
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.