Lookup table values using day-of-the-year integers

[David Blackman]

If there's a harder way to do something, I usually search tirelessly until I find it.

I have a people dbs with a form that includes ‘date of birth’ and ‘age’ calculated by a function. Additionally, I wanted to add the person’s astrological sign, looked up from source table, 'stbl_Sign':



So far the only way I have been able to successfully look up the ‘Sign’ is by converting ‘date of birth’ and the 'Signs' various ‘from’ and ‘to’ dates to integers based on their day of the year:

Public Function fn_Sign(dteDOB As Date) As String
    
    On Error Resume Next
    
    Dim strSign As String
    Dim intDOB As Integer
    Dim strmsg As String
    
    intDOB = fn_Yearday(dteDOB)
    
    If intDOB >= 357 Or intDOB < 19 Then
        fn_Sign = "Capricorn"
        Exit Function
    End If
    
    strSign = DLookup("Sign", "stbl_Sign", "[doyFrom]<=" & intDOB & " AND [doyTo]>=" & intDOB)
     
    fn_Sign = strSign
        
End Function

Day of the year is computed by this function:

Public Function fn_Yearday(dat As Date) As Integer

    'Purpose: does the same as day for month
    'Use to access an array where the index corresponds to days
    'starts with 1 for the january first
    '?Yearday(#1/12/2004#) '>> 12

    fn_Yearday = DateDiff("d", CDate("1/1/" & Year(dat)), dat) + 1

End Function

Can anyone suggest a more elegant solution?

[jforbes]

DatePart() with an interval of "y" will give you the day of the year.

DatePart ("y", dSomeDate)

But that approach will need to take leap year into account as the window for a Sign will shift by one day for dates after February 28th on a leap year.
You can test for a leap year with something like this:

Public Function isLeapYear(ByRef dDate As Date) As Boolean
    isLeapYear = (DatePart("yyyy", dDate) Mod 4 = 0) And (Not (DatePart("yyyy", dDate) Mod 100 = 0) Or (DatePart("yyyy", dDate) Mod 400 = 0))
End Function

[jforbes]

I got into this one, here is what I came up with:

Public Function isLeapYear(ByRef dDate As Date) As Boolean
    isLeapYear = (DatePart("yyyy", dDate) Mod 4 = 0) And (Not (DatePart("yyyy", dDate) Mod 100 = 0) Or (DatePart("yyyy", dDate) Mod 400 = 0))
End Function

Public Function fn_Sign(dteDOB As Date) As String
 
     Dim intDOB As Integer
 
     intDOB = DatePart("y", dteDOB)
     If isLeapYear(dteDOB) And intDOB > 60 Then intDOB = intDOB - 1
     
    Select Case intDOB
        Case Is < 20
            fn_Sign = "Capricorn"
        Case Is < 50
            fn_Sign = "Aquarius"
        Case Is < 80
            fn_Sign = "Pisces"
        Case Is < 110
            fn_Sign = "Aries"
        Case Is < 141
            fn_Sign = "Taurus"
        Case Is < 172
            fn_Sign = "Gemini"
        Case Is < 204
            fn_Sign = "Cancer"
        Case Is < 235
            fn_Sign = "Leo"
        Case Is < 266
            fn_Sign = "Virgo"
        Case Is < 296
            fn_Sign = "Libra"
        Case Is < 326
            fn_Sign = "Scorpio"
        Case Is < 356
            fn_Sign = "Sagittarius"
        Case Else
            fn_Sign = "Capricorn"
    End Select
        
 End Function