User Defined Function - how to handle multiple and complex conditions

[dieffen]

I have to report if health care personnel are up to date on vaccinations by the CDC definition. I wanted to create a UDF to use in a query that would return a 1 if up-to-date and a 0 if they are not. I'm getting #Error on records that don't meet the criteria...also, some of the parameters are null because they didn't get the primary vax or recommended boosters and not sure if how to best handle.

Up-to-date criteria

Psuedo-code
Age <50
50 and [Primary Series Complete]="Yes"
• If "Janssen" and received 1st booster more than 2 months from primary then up-to-date.
• if Pfizer of Moderna and received first booster more than 5 months from 2nd dose of primary, then up-to-date.
50 or over
• Primary Series Complete, if J&J and received 1st booster more than 2 months from primary and received 2nd booster 4 months after 1st booster then up-to-date.
• Primary Series Complete, if Pfizer of Moderna and received first booster more than 5 months from 2nd dose of primary and received 2nd booster 4 months after 1st booster, then up-to-date.

'Any of the parameters could be NULL. How to best handle when dates and manufacturer values are NULL?

Public Function UTD_EMP(Optional prim_comp As Variant, Optional prim_manu As Variant, Optional prim_first_dt As Variant, Optional prim_last_dt As Variant, _
            Optional bst_one_dt As Variant, Optional bst_two_dt As Variant, Optional dob As Date)
Dim int_age As Integer ' I have an Age Function to calc from DOB
Dim dt_prim_flg As Boolean
Dim dt_bst1_flg As Boolean
Dim dt_bst2_flg As Boolean
Dim intResult As Integer
dt_prim = CDate(prim_last_dt)
dt_bst1 = CDate(bst_one_dt)
dt_bst2 = CDate(bst_two_dt)
dt_prim_flg = IsDate(prim_first_dt) 'Is the primary dose populated
'HOW TO BEST HANDLE WHEN VAX AND/OR BOOSTER DATES ARE NULL? 
Select Case prim_comp = "Yes"
        ' Under 50 
            Case Age(dob) < 50
                Select Case prim_manu = "Janssen" 'Received the Janssen vax 
                    Case DateDiff("m", CDate(prim_first_dt), CDate(bst_one_dt)) >= 2 'Booster was given 2+ months after primary vax. Booster date could be null.
                        intResult = 1
                    Case DateDiff("m", CDate(prim_first_dt), Now()) < 2 'Account for those that just got primary vax but hasn't been 2 months yet for booster
                        intResult = 1
                    Case Else
                        intResult = 0
                End Select
Select Case prim_manu = "Moderna" Or prim_manu = "Pfizer_BioNTech"
                    Case DateDiff("m", CDate(prim_last_dt), CDate(bst_one_dt)) >= 5 'received booster 5+ months after primary vax.  Booster date could be null.
                        intResult = 1
                    Case DateDiff("m", CDate(prim_last_dt), Now()) < 5 'Account for those that just got primary vax but hasn't been 5 months yet for booster
                        intResult = 1
                    Case Else
                        intResult = 0
                End Select
' 50 and Over,  Age 50 and older should get 2nd booster 
           Case Age(dob) >= 50
                Select Case prim_manu = "Janssen"
                    Case (DateDiff("m", CDate(prim_first_dt), CDate(bst_one_dt)) >= 2 Or DateDiff("m", CDate(prim_first_dt), Now()) < 2) And _'First Booster
                         (DateDiff("m", CDate(bst_one_dt), CDate(bst_two_dt)) >= 4 Or DateDiff("m", CDate(bst_one_dt), Now()) < 4)'Booster 2 or not 4 months since first booster 
                        intResult = 1
                    Case Else
                        intResult = 0
                End Select
Select Case prim_manu = "Moderna" Or prim_manu = "Pfizer_BioNTech"
                    Case (DateDiff("m", CDate(prim_last_dt), CDate(bst_one_dt)) >= 5 Or DateDiff("m", CDate(prim_last_dt), Now()) < 5) And _
                         (DateDiff("m", CDate(bst_one_dt), CDate(Mid(bst_two_dt, 6, 2) & "/" & Mid(bst_two_dt, 9, 2) & "/" & Left(bst_two_dt, 4))) >= 4 Or DateDiff("m", CDate(bst_one_dt), Now()) < 4)
                         '(DateDiff("m", CDate(bst_one_dt), CDate(bst_two_dt)) >= 4 Or DateDiff("m", CDate(bst_one_dt), Now()) < 4)
                        intResult = 1
                    Case Else
                        intResult = 0
                End Select
Case Else
    intResult = 0
End Select
UTD_EMP = intResult
End Function

[arnelgp]

you can easily use a Truth table in excel, listing each conditions and the expected result for the condition.
automatically if neither conditions are met the return is 0.