Dates

A

Ang

I have a form that has 2 dates on it. The first date is PayPeriodEnding; the
second one is DateWorkPerformed.

The Pay Period Ending is chosen by a drop down menu, the Date Work Performed
is entered manually.

What can I do to make sure that the Date Work Performed is a correct date
for the Pay Period Ending they have entered?

Example:
PayPeriodEnding 06/10/06
DateWorkPerformed must be fall in between 05/28/06-06/10/06

Please Help! Please be kind first database built out of school :)
 
K

Ken Sheridan

Do I take it from the example you've cited that the valid range is the 14
days of which the PayPeriodEnding is the last day? If so then you can
validate the entry in the DateWorkPerformed text box with some code in its
BeforeUpdate event procedure:

Dim dtmStart As Date, dtmEnd As Date, dtmWorkPerformed As Date
Dim strMessage As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a pay period end date has been selected
If IsNull(Me.PayPeriodEnding) Then
' is DateWorkPerformed control also Null?
If Not IsNull(ctrl) Then
' if not, inform user if a pay period end date
' has not been selected and cancel update
strMessage = "A pay period end date must be selected first."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Cancel = True
End If
Else
dtmEnd = CDate(Me.PayPeriodEnding)
dtmStart = dtmEnd - 13
dtmWorkPerformed = CDate(ctrl)
' if date entered is out of range inform user and cancel update
If Not (dtmWorkPerformed >= dtmStart And dtmWorkPerformed <= dtmEnd)
Then
strMessage = "Date must be between " & dtmStart & _
" and " & dtmEnd & "."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If

Ken Sheridan
Stafford, England
 
A

Ang

Below is what I entered....I am getting an error "Comile error: method or
data member not found." Thank you for your help!

Private Sub DATE_WORK_PERFORMED_BeforeUpdate(Cancel As Integer)
Dim dtmStart As Date, dtmEnd As Date, dtmWorkPerformed As Date
Dim strMessage As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a pay period end date has been selected
If IsNull(Me.PayPeriodEnding) Then
' is DateWorkPerformed control also Null?
If Not IsNull(ctrl) Then
' if not, inform user if a pay period end date
' has not been selected and cancel update
strMessage = "A pay period end date must be selected first."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Cancel = True
End If
Else
dtmEnd = CDate(Me.PayPeriodEnding)
dtmStart = dtmEnd - 13
dtmWorkPerformed = CDate(ctrl)
' if date entered is out of range inform user and cancel update
If Not (dtmWorkPerformed >= dtmStart And dtmWorkPerformed <= dtmEnd)
Then
strMessage = "Date must be between " & dtmStart & _
" and " & dtmEnd & "."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If

End Sub
 
A

Ang

Below is what I entered...I keep geting this error Complie Error : Method or
data member not found. Please help!?!?!?!
 
A

Ang

This is yellow:
Private Sub DATE_WORK_PERFORMED_BeforeUpdate(Cancel As Integer)
and this is selected:
If IsNull(Me.PayPeriodEnding) Then
 
D

Douglas J Steele

What is PayPeriodEnding: the name of a field in the underlying recordset,
the name of a control on the form, or both?

If it's both (i.e.: you've got a text box named PayPeriodEnding that has
PayPeriodEnding as its control source), try renaming the text box. Make sure
that you haven't mistyped the name of the control.
 
A

Ang

Ok that one was fixed now I am getting this error:

If Not (dtmWorkPerformed >= dtmStart And dtmWorkPerformed <= dtmEnd)
 
A

Ang

Well DUH! lol

I had hit enter and the Then was on another line. Thank you so very much for
your help!
 
Top