date problem

[SuzyQ]

I have the follow code on a date text box format is short date, control
source is date type field. Access 2000. I've stepped though the code and it
does not matter whether the date entered is in the range or not, the if
me.dateworked... statement is evaluated to true regardless, and the update is
cancelled and date is not accepted. If the user is using a filter on the
parent form, then when they are entering timecards the dates must be within
the filtered range. I have stepped through the code and the values seem to
be correct to do what I intend, but I can't get past this field.

Private Sub DateWorked_BeforeUpdate(Cancel As Integer)
If Me.FilterOn = True Then
If Not (Me.DateWorked >= Me.Parent.cmbPayPeriod.Column(1) And
Me.DateWorked <= Me.Parent.cmbPayPeriod.Column(2)) Then
MsgBox "Date not within range selected" & vbCrLf & "Remove
filter or change date"
Cancel = -1
End If
End If
End Sub

 

[Klatuu]

It looks right, but I am suspicious of the Not in one comparison and it
missing in the other.

Here is a little function I keep in a Standard module so I can call it from
anywhere. It works with any data type; however, the data types must all be
the same.

Public Function IsBetween(varCheckVal As Variant, varLowVal As Variant,
varHighVal As Variant) As Boolean
IsBetween = varCheckVal >= varLowVal And varCheckVal <= varHighVal
End Function

You might try this:

Private Sub DateWorked_BeforeUpdate(Cancel As Integer)
If Me.FilterOn = True Then
If Not IsBetween(Me.DateWorked, Me.Parent.cmbPayPeriod.Column(1),
Me.Parent.cmbPayPeriod.Column(2)) Then
MsgBox "Date not within range selected" & vbCrLf & "Remove
filter or change date"
Cancel = True
End If
End If
End Sub

Note I change Cancel = -1 to Cancel = True
It is easier to read that way

 

[SuzyQ]

Not sure what you're suspicious of. The first comparison checks to see if
there is a filter set. If there is, it is based on the from and to dates of
the combo box in the parent form. Column 0 of the record source is a string
of fromDate To toDate, column 1 is fromDate column 2 is toDate. The Not
completely encapsulates the second comparison "(Me.DateWorked >=
Me.Parent.cmbPayPeriod.Column(1) And
Me.DateWorked <= Me.Parent.cmbPayPeriod.Column(2)) " I was still having
problems with your function as well until I changed my code to the following,
and now it works fine.


If Me.FilterOn = True Then
If Not IsBetween("#" & Me.DateWorked & "#", "#" &
Me.Parent.cmbPayPeriod.Column(1) & "#", "#" &
Me.Parent.cmbPayPeriod.Column(2) & "#") Then
MsgBox "Date not within range selected" & vbCrLf & "Remove
filter or change date"
Cancel = True
End If
End If