Consulting

Results 1 to 2 of 2

Thread: User Defined Function - how to handle multiple and complex conditions

  1. #1
    VBAX Newbie
    Joined
    Jul 2022
    Posts
    1
    Location

    User Defined Function - how to handle multiple and complex conditions

    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
    Last edited by Aussiebear; 07-12-2022 at 02:07 PM. Reason: Added code tags to supplied code

  2. #2
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •