Validation Rule for specific answer

[GraemeG]

Hello,



I have a rule to limit the response a user can input on the form.
The code works in terms of not allowing a date prior to current year, or does not allow it to exceed current year + 20. But if a user accidently reponds then deletes and trys to move on, it states a 94 error: Null value debug.

Private Sub Entrance_Doors___Flats__Renew_Year__20_LE_AfterUpdate()
If Me.[Entrance Doors - Flats (Renew Year) 20 LE] < Format(Date, "yyyy") Then
    MsgBox "Renew Year Invalid:  Less than current year!"
    End If
If CLng(Me.[Entrance Doors - Flats (Renew Year) 20 LE] >= (Format(Date, "yyyy") + 20)) Then
    MsgBox "Renew Year Invalid:  Exceeds life expectancy!"
    End If
End Sub

Any help much appreciated

[jgelpi16]

Try assigning the value of your fields to a variable first. Sometimes this helps me.

[GraemeG]

I thought I has done this with the CLng?

[jgelpi16]

Dim vEntranceDoors

vEntranceDoors = [Entrance Doors - Flats (Renew Year) 20 LE]
If vEntranceDoors < Format(Date, "yyyy") then
     'Display Messagebox
End If
If DatePart("yyyy", vEntranceDoors) >= (DatePart("yyyy", Date) + 20) then
     'Display Messagebox
End If

[GraemeG]

Dim vEntranceDoors
 
vEntranceDoors = [Entrance Doors - Flats (Renew Year) 20 LE]
If vEntranceDoors < Format(Date, "yyyy") then
     'Display Messagebox
End If
If DatePart("yyyy", vEntranceDoors) >= (DatePart("yyyy", Date) + 20) then
     'Display Messagebox
End If

Thanks - that has resolved the 94 null error.
But has now stopped the second part (date + 20 years) from working. i can enter 3000 and it wont flag up exceeds life expectancy

[jgelpi16]

Try:

Dim vEntranceDoors
Dim vCompareDate

vCompareDate = Datpart("yyyy", Date) + 20
vEntranceDoors = [Entrance Doors - Flats (Renew Year) 20 LE]

If vEntranceDoors < Format(Date, "yyyy") then
     'Display Messagebox
End If
If DatePart("yyyy", vEntranceDoors) >= vCompareDate then
     'Display Messagebox
End If

[GraemeG]

Sorry still no luck. Still allows a great entry.

[jgelpi16]

What happens if you enter a less than 20 year entry? Is the text box where you enter the date the same name still?

[GraemeG]

It works fine for anything prior to current year - flags up < Current Year.
But lets you enter anything from 2011 to infinate without error.
Yep renamed:

Private Sub EntranceDoorsFlatsRenewYear_AfterUpdate()
Dim vEntranceDoors
Dim vCompareDate
vCompareDate = DatePart("yyyy", Date) + 20
vEntranceDoors = [EntranceDoorsFlatsRenewYear]
If vEntranceDoors < Format(Date, "yyyy") Then
     MsgBox "Renew Year Invalid:  < Current Year!"
End If
If DatePart("yyyy", vEntranceDoors) >= vCompareDate Then
     MsgBox "Renew Year Invalid:  Exceeds Life Expectancy!"
End If
End Sub

[jgelpi16]

Ok, try...

Private Sub EntranceDoorsFlatsRenewYear_AfterUpdate()
Dim vEntranceDoors
Dim vCompareDate
vCompareDate = DatePart("yyyy", Date) + 20
vEntranceDoors = EntranceDoorsFlatsRenewYear.Value
If vEntranceDoors < Format(Date, "yyyy") Then
     MsgBox "Renew Year Invalid:  < Current Year!"
End If
If DatePart("yyyy", vEntranceDoors) >= vCompareDate Then
     MsgBox "Renew Year Invalid:  Exceeds Life Expectancy!"
End If
End Sub

[jgelpi16]

Sorry, disregard my code on last post...I just tested code in DB, there were some syntax issues. Below code should work.

Dim vEntranceDoors
Dim vCompareDate
Dim vCurrDate

vCurrDate = DatePart("yyyy", Date)
vCompareDate = DatePart("yyyy", Date) + 20
vEntranceDoors = DatePart("yyyy", EntranceDoorsFlatsRenewYear.Value)
If vEntranceDoors < vCurrDate Then
     MsgBox "Renew Year Invalid:  < Current Year!"
End If
If vEntranceDoors >= vCompareDate Then
     MsgBox "Renew Year Invalid:  Exceeds Life Expectancy!"
End If

...Guess that's what I get for not trying code out in developer prior to speaking on it....

[GraemeG]

Sorry thanks for all your help. but still not luck.

i did have this code:
But it falgs up all msgbox's regardless of the answer, but does stop the 94 error also.

Private Sub Entrance_Doors___Flats__Renew_Year__20_LE_AfterUpdate()
If Me.[Entrance Doors - Flats (Renew Year) 20 LE] < Format(Date, "yyyy") Then
    MsgBox "Renew Year Invalid: < Current Year!"
    End If
If Not IsNull(Me.[Entrance Doors - Flats (Renew Year) 20 LE]) Then
If Me.[Entrance Doors - Flats (Renew Year) 20 LE] = Format(Date, "yyyy") + 20 Then
    MsgBox "Renew Year Invalid: Exceeds life expectancy!"
End If
End Sub

[jgelpi16]

Did you try the code posted at 3:31pm? I ran it and it appeared to work just fine.

[GraemeG]

Did you try the code posted at 3:31pm? I ran it and it appeared to work just fine.

Yeah just tried this one and now it just flags Msgbox "Renew Year Invalid: < Current Year no matter what I enter

[jgelpi16]

What entry are you expecting from users? Are you expecting a full date (i.e. 02/08/2011) or just the year? Not sure why the below code doesn't work if you're entering full years. I just tried it again and it works.

Dim vEntranceDoors
Dim vCompareDate
Dim vCurrDate

vCurrDate = DatePart("yyyy", Date)
vCompareDate = DatePart("yyyy", Date) + 20
vEntranceDoors = DatePart("yyyy", EntranceDoorsFlatsRenewYear.Value)
If vEntranceDoors < vCurrDate Then
     MsgBox "Renew Year Invalid:  < Current Year!"
End If
If vEntranceDoors >= vCompareDate Then
     MsgBox "Renew Year Invalid:  Exceeds Life Expectancy!"
End If

In dealing with dates you may find this link helpful.
Format:
http://www.techonthenet.com/access/f...ate/format.php

DatePart:
http://www.techonthenet.com/access/f...e/datepart.php