Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 :)
 
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
 
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
 
Below is what I entered...I keep geting this error Complie Error : Method or
data member not found. Please help!?!?!?!
 
This is yellow:
Private Sub DATE_WORK_PERFORMED_BeforeUpdate(Cancel As Integer)
and this is selected:
If IsNull(Me.PayPeriodEnding) Then
 
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.
 
Ang said:
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
Click to expand...

Create an auxiliary 'calendar' table with all valid values for
DateWorkPerformed and their respective PayPeriodEnding values e.g.

INSERT INTO Calendar (DateWorkPerformed, PayPeriodEnding)
VALUES (#2006-05-28#, #2006-06-10#)
;
INSERT INTO Calendar (DateWorkPerformed, PayPeriodEnding)
VALUES (#2006-05-29#, #2006-06-10#)
;
INSERT INTO Calendar (DateWorkPerformed, PayPeriodEnding)
VALUES (#2006-05-30#, #2006-06-10#)
;
etc

although you may find this easier to construct with a spreadsheet. You
may want to omit weekends, public holidays, etc as appropriate to the
enterprise. You can load up several years' data and the table will
still be relatively small.

With this table in place I'm sure you can see how a DateWorkPerformed
date can easily be validated against PayPeriodEnding. You may even take
the opportunity to restructure your other tables accordingly.

Jamie.

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

If Not (dtmWorkPerformed >= dtmStart And dtmWorkPerformed <= dtmEnd)
 
Well DUH! lol

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