Validation Rule based on bound comboboxes

[Billiam]

I have a date/Time field called DateWorked on a form called f_TimeSheet. also
on the form are 2 comboboxes cboPPF (pay period from, Date/Time format) and
cboPPTo (PayPeriodTo, Date/Time format).

I have been trying to enter a validation rule at the table (t_Timesheet)
level for
DateWorked so that dateWorked>= cboPPF AND <=PPTo...

I cannot get it to work, and I am wondering if I should be referencing the
comboxes a certain way? I am trying to ensure the dateWorked falls in the
PayPeriod being reported on in the form.

Any help appreciated!

Billiam

 

[Klatuu]

I would not use a field level validation rule. I would use the Before Update
event of the form to check for the correct date ranges:

If Not IsBetween(Me.dateWorked, Me.cboPPF, Me.PPTo) Then
MsgBox "Date Worked must be Between Pay Period From and Pay Period To"
Cancel = True
End If

Here is the function I used:

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

 

[Billiam]

Hi,

I am not sure what is wrong, however, when i choosed a dateWorked date
within the payperiod, i am getting the error message 'date worked must be
between pay period from and pay Perio To".

I went into the form 9f_timesheet, and chose the before update event, code
bulider, copied the expression, also created the function 'isBetween'...any
ideas what I have done wrong?

Here is what I have for the before update event (form clicked in top left
corner to insure form properties are listed)

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsBetween(Me.DateWorked, Me.cboPPF, Me.cboPPTo) Then
MsgBox "Date Worked must be Between Pay Period From and Pay Period To"
Cancel = True
End If

End Sub

and then the function:

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

Sorry, I am very new to this and am probably doing something wrong? I did
have to make a small correction to cboPPTo fro PPTo as you had, but that is
the correct name of the combobox.
Thanks for any help on this!
Billiam

 

[Klatuu]

It may be that your date values are being seen as text. You might try:

If Not IsBetween(Cdate(Me.DateWorked), Cdate(Me.cboPPF), Cdate(Me.cboPPTo))
Then

 

[Billiam]

hi,
That still generates the same error..I am positive i am using a date in the
payperiod...I have verified that all fields are Date/Time longdate format...
i am wondering if it is beacause the bound column of the cboPPF and cboPPTo
are the autonumber ID's and therefore an autogenerated number?

If you do not mind sticking with me on this, I am happy to try anything to
get this working...

Thanks again for your help!
Billiam

 

[Klatuu]

i am wondering if it is beacause the bound column of the cboPPF and cboPPTo
are the autonumber ID's and therefore an autogenerated number?

Yes. If the date field is not the bound column, you will need to refer to
the column that is the data field. Remember that column numbering starts at
0, so if your date is, for example, the second column, it should be
referenced as:

Me.cboPPF.Column(1)

 

[Billiam]

Hi,

That did the trick! for those following this post, I was using long date
format in my cboPPf and cboPPTo, and so i think that is why the values are
being passed as text.

Here are the module and function as I am using them, which works great:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsBetween(CDate(Me.DateWorked), CDate(Me.cboPPF.Column(1)),
CDate(Me.cboPPTo.Column(1))) Then
MsgBox "Date Worked must be Between Pay Period From and Pay Period To"
Cancel = True

End If

End Sub

and the Function:

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

Thank you again for sticking with me on this, it has been a great learning
experience!

Hope you have a great week!

best regards,
Billiam