validation rule

  • Thread starter Thread starter Deepak
  • Start date Start date
D

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])
Click to expand...

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
 
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


Deepak said:
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])
Click to expand...

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
Click to expand...
 
Thanks Graham for ur help

Graham Mandeno said:
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


Deepak said:
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])
Click to expand...

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
Click to expand...
Click to expand...
 
Back
Top