validation rule

[Deepak]

Hi

I have created a query [Acceptable Dates] where i m calculating two dates A1
and A2

Now i have a form Premium Paid, where i m inputting the premiums paid by
users. The form has three entries viz

Policy No
Premium Paid Date
Premium

the user has to enter a policy no. and select the date when the premium was
paid

i have created a validation rule for premium paid date on the form itself in
the property sheet like this

= DLookUp("[A1]","[Acceptable Dates]","[Policy Number]=" & [Forms]![Premium Paid]![Policy No]) And <=DLookUp("[A2]","[Acceptable Dates]","[Policy Number]=" & [Forms]![Premium Paid]![Policy No])

Basically i want that user will be able to select any date between the dates
a1 and a2 which i have calculated in query, otherwise it should display error
message.

But the problem is that i m not able to enter some of dates between the two.

Kindly help is my method correct or is there any other way to do this.

Thanks
Deepak

 

[Graham Mandeno]

Hi Deepak

This expression is very complex for a validation rule, and using two
DLookups would be very slow as well.

I suggest you write a function to validate the date as follows:

Public Function IsAcceptableDate( _
dt as Variant, _
PolicyNum as Long ) as Boolean
Dim rs as DAO.Recordset
On Error Goto ProcErr
' include the following block only if Null is acceptable
If IsNull(dt) then
IsAcceptableDate = True
Goto ProcEnd
End If
If Not IsDate(dt) then Goto ProcEnd
Set rs = CurrentDb.OpenRecordset( _
"Select A1, A2 from [Acceptable Dates] " _
& "where [Policy Number]=" & PolicyNum
If not rs.EOF then
IsAcceptableDate = (dt >= rs!A1) and (dt <= rs!A2)
End If
ProcEnd:
On Error Resume Next
If Not rs is nothing then
rs.Close
Set rs = Nothing
End If
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function

Now you can use the function in the BeforeUpdate event procedure for your
[Premium Paid Date] textbox:

Private Sub Premium_Paid_Date_BeforeUpdate(Cancel as Integer)
If Not IsAcceptableDate([Premium Paid Date], [Policy No]) Then
Cancel = True
MsgBox "Invalid payment date"
End If
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi

I have created a query [Acceptable Dates] where i m calculating two dates
A1
and A2

Now i have a form Premium Paid, where i m inputting the premiums paid by
users. The form has three entries viz

Policy No
Premium Paid Date
Premium

the user has to enter a policy no. and select the date when the premium
was
paid

i have created a validation rule for premium paid date on the form itself
in
the property sheet like this

= DLookUp("[A1]","[Acceptable Dates]","[Policy Number]=" &
[Forms]![Premium Paid]![Policy No]) And <=DLookUp("[A2]","[Acceptable
Dates]","[Policy Number]=" & [Forms]![Premium Paid]![Policy No])

Basically i want that user will be able to select any date between the
dates
a1 and a2 which i have calculated in query, otherwise it should display
error
message.

But the problem is that i m not able to enter some of dates between the
two.

Kindly help is my method correct or is there any other way to do this.

Thanks
Deepak

 

[Deepak]

Thanks Graham for ur help

Hi Deepak

This expression is very complex for a validation rule, and using two
DLookups would be very slow as well.

I suggest you write a function to validate the date as follows:

Public Function IsAcceptableDate( _
dt as Variant, _
PolicyNum as Long ) as Boolean
Dim rs as DAO.Recordset
On Error Goto ProcErr
' include the following block only if Null is acceptable
If IsNull(dt) then
IsAcceptableDate = True
Goto ProcEnd
End If
If Not IsDate(dt) then Goto ProcEnd
Set rs = CurrentDb.OpenRecordset( _
"Select A1, A2 from [Acceptable Dates] " _
& "where [Policy Number]=" & PolicyNum
If not rs.EOF then
IsAcceptableDate = (dt >= rs!A1) and (dt <= rs!A2)
End If
ProcEnd:
On Error Resume Next
If Not rs is nothing then
rs.Close
Set rs = Nothing
End If
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function

Now you can use the function in the BeforeUpdate event procedure for your
[Premium Paid Date] textbox:

Private Sub Premium_Paid_Date_BeforeUpdate(Cancel as Integer)
If Not IsAcceptableDate([Premium Paid Date], [Policy No]) Then
Cancel = True
MsgBox "Invalid payment date"
End If
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi

I have created a query [Acceptable Dates] where i m calculating two dates
A1
and A2

Now i have a form Premium Paid, where i m inputting the premiums paid by
users. The form has three entries viz

Policy No
Premium Paid Date
Premium

the user has to enter a policy no. and select the date when the premium
was
paid

i have created a validation rule for premium paid date on the form itself
in
the property sheet like this

= DLookUp("[A1]","[Acceptable Dates]","[Policy Number]=" &
[Forms]![Premium Paid]![Policy No]) And <=DLookUp("[A2]","[Acceptable
Dates]","[Policy Number]=" & [Forms]![Premium Paid]![Policy No])

Basically i want that user will be able to select any date between the
dates
a1 and a2 which i have calculated in query, otherwise it should display
error
message.

But the problem is that i m not able to enter some of dates between the
two.

Kindly help is my method correct or is there any other way to do this.

Thanks
Deepak