Find a number

[67flyer]

Not sure how to do a search on this one so here it is.

I have a paytable that has years and dollar amounts to each year. Years 1-4 are consecutive and after 4 they increase by 2 up to year 26. Year 26 is the max amount you can get. ie. 2=20.00, 3=22.00, 4=26.00, 6 = 30.00, 8 = 35.60, 10= 41.00 .... and 26=100.00

On a form I calculate a date that gives me the number of years, and then i take the years and perform a dlookup on the table for the amount associated with the years. If the years equal 8 it gives 35.60 but if there is no direct association it is blank, say years equals 7 or 9 i don't get a result.

I could have an unbound box that would perform a calc on the years and do a round down (never would perform a round up). Then perform the dlookup using that new number. Or if years are not a even number(not sure how to check for an even number) subtract 1 to get to an even number. This would require a check for 1-4 and then a check for =>26 any thing over 26 would always be 26.

Thanks,

 

[MarkK]

Here's code you might like...

Sub Test()
[COLOR="Green"]'  Shows results for the number range you specified[/COLOR]
   Dim i As Integer
   For i = 1 To 29
      Debug.Print i, GetFunnyNumber(i)
   Next i
End Sub

Function GetFunnyNumber(num As Integer) As Integer
   If num < 5 Then
      GetFunnyNumber = num
   ElseIf num < 27 Then
[COLOR="Green"]      'returns only even numbers here since it always subtracts the remainder
      'which is only present for odd numbers[/COLOR]
      GetFunnyNumber = num - num Mod 2
   Else
      GetFunnyNumber = 26
   End If
End Function

 

[67flyer]

Thanks lagbolt,

figured it out before I saw your post. This seems to work:

=IIf([tmpyrssvc]<=4,[tmpyrssvc],IIf([tmpyrssvc]>DMax("years","tblpay","[grade]=forms!frmpersonnel!tmpgrade"),DMax("years","tblpay","[grade]=forms!frmpersonnel!tmpgrade"),IIf([tmpeven]=1,[tmpyrssvc]-1,[tmpyrssvc])))