Validation Rule for specific answer

[GraemeG]

Thanks.

Yes I want them to answer a year.

So they would input i.e. 2015 which would be ok to continue.
But if they answered <2011 then I want the first msgbox to appear stating <current year. And if they answered 2040 then this exceeds the life expectancy given an install of 2011 + 20 = 2031 so anything entered above 2031 would flag up the second message Exceeds Life Expectancy.



But the first message flags up regardless of the answer

[jgelpi16]

Ok, if you are just expecting a year input then you just need to run code as follows:

Dim vEntranceDoors
Dim vCompareDate
Dim vCurrDate

vCurrDate = DatePart("yyyy", Date)
vCompareDate = DatePart("yyyy", Date) + 20
vEntranceDoors = 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

This should work...

[GraemeG]

Ta.
But now its doing the opposite. It now flags up the Exceeds Life Expectancy Msgbox regardless of the answer.

[jgelpi16]

Ok, what is happening is this. Since you are going strictly off year you should treat these years as numbers not dates. Format EntranceDoorsFlatsRenewYear as a Fixed number with 0 decimal places. Dim all your variables as Long (See code below). This should work as expected. Make sure you save all changes before testing the code.

Dim vEntranceDoors, vCompareDate, vCurrDate As Long

vCurrDate = DatePart("yyyy", Date)
vCompareDate = DatePart("yyyy", Date) + 20
vEntranceDoors = 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

[GraemeG]

Sorry still no luck.

I have attached an example.

[GraemeG]

RESOLVED:

Private Sub EntranceDoorsFlatsRenewYear_AfterUpdate()
If IsNull(EntranceDoorsFlatsRenewYear.Value) Then
    Me.[EntranceDoorsFlatsRenewYear] = ""
ElseIf Me.[EntranceDoorsFlatsRenewYear] < Format(Date, "yyyy") Then
    MsgBox "Renew Year Invalid:  Less than current year!"
ElseIf CLng(Me.[EntranceDoorsFlatsRenewYear] >= CLng(Format(Date, "yyyy") + 20)) Then
    MsgBox "Renew Year Invalid:  Exceeds life expectancy!"
End If
End Sub