If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
validation rule
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 |
#2
|
|||
|
|||
validation rule
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" wrote in message ... 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 |
#3
|
|||
|
|||
validation rule
Thanks Graham for ur help
"Graham Mandeno" wrote: 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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|