CODEWITHSUNDEEP
vbams-accessms-access-2013
Johnny Bones
Johnny Bones

Reputation: 8402

Access function with NULL date values

I haven't touched Access in several years, so my VBA is rusty. I'm trying to do a complex If/Then/Else function in Access, so I figured I'd turn it into a custom function instead of an overly complex IIF statement.

What I have for my function is this:

Option Compare Database
Option Explicit

Public Function fnM01Errors(AUTO_CLM_ID As String, MED_CLM_ROLL_IND As String, CLM_FWD_EFF_DT As Date, CLM_FWD_CAN_DT As Date, CCF_PKG_TY As String, CLM_FWD_APPLY_IND As String, MED_EFF_DATE As Date)

If [AUTO_CLM_ID] = "Y" And [MED_CLM_ROLL_IND] <> "R" Or ([AUTO_CLM_ID] = "N" Or [AUTO_CLM_ID] Like "*-*") And [MED_CLM_ROLL_IND] <> "N" Then
    fnM01Errors = "AUTO CLM_FWD_IND does not match MED_CLM_ROLL_IND"

If CLM_FWD_EFF_DT <> Null And CLM_FWD_CAN_DT <> Null And CCF_PKG_TY = "HRA" And CLM_FWD_APPLY_IND = "Y" And MED_EFF_DATE >= CLM_FWD_CAN_DT And MED_CLM_ROLL_IND = "N" Then
    fnM01Errors = "No Error"

If CLM_FWD_EFF_DT <> Null And CLM_FWD_CAN_DT <> Null And CCF_PKG_TY = "HSA" And CLM_FWD_APPLY_IND = "Y" And MED_EFF_DATE >= CLM_FWD_CAN_DT And MED_CLM_ROLL_IND = "N" Then
    fnM01Errors = "INVALID_ePro_ERROR"

If CLM_FWD_EFF_DT <> Null And CLM_FWD_CAN_DT <> Null And CCF_PKG_TY = "HSA" And CLM_FWD_APPLY_IND = "Y" And MED_EFF_DATE >= CLM_FWD_CAN_DT And MED_CLM_ROLL_IND = "R" Then
    fnM01Errors = "AUTO CLM_FWD_IND does not match MED_CLM_ROLL_IND"

End Function

And in my query, I'm calling it like this:

Error: fnM01Errors([AUTO-CLM-ID],[MED_CLM_ROLL_IND],Nz([CLM_FWD_EFF_DT],#12/31/2099#),Nz([CLM_FWD_CAN_DT],#12/31/2099#),[CCF-PKG-TY],[MED_CCF_IND],Nz([MED_EFF_DATE],#12/31/2099#))

It keeps giving me an error:

Data Type Mismatch in query expression

I assumed it was because it was finding NULL dates, of which there are a lot, so that's when I added the Nz() functions to the date fields. However, I'm not even sure if that's right.

Can anyone tell me if this code is "logically" correct before I go pulling apart the dataset to look for a needle in a haystack?

Upvotes: 0

Views: 2902

Answers (3)

Gustav
Gustav

Reputation: 55841

You can reduce this:

Public Function fnM01Errors( _
    AUTO_CLM_ID As String, _
    MED_CLM_ROLL_IND As String, _
    CLM_FWD_EFF_DT As Variant, _
    CLM_FWD_CAN_DT As Variant, _
    CCF_PKG_TY As String, _
    CLM_FWD_APPLY_IND As String, _
    MED_EFF_DATE As Variant)

    If [AUTO_CLM_ID] = "Y" And [MED_CLM_ROLL_IND] <> "R" Or ([AUTO_CLM_ID] = "N" Or [AUTO_CLM_ID] Like "*-*") And [MED_CLM_ROLL_IND] <> "N" Then
        fnM01Errors = "AUTO CLM_FWD_IND does not match MED_CLM_ROLL_IND"
    ElseIf Not IsNull(CLM_FWD_EFF_DT + CLM_FWD_CAN_DT) Then
        If CCF_PKG_TY = "HRA" And CLM_FWD_APPLY_IND = "Y" And MED_EFF_DATE >= CLM_FWD_CAN_DT And MED_CLM_ROLL_IND = "N" Then
            fnM01Errors = "No Error"
        ElseIf CCF_PKG_TY = "HSA" And CLM_FWD_APPLY_IND = "Y" And Nz(MED_EFF_DATE >= CLM_FWD_CAN_DT, False) Then
            If MED_CLM_ROLL_IND = "N" Then
                fnM01Errors = "INVALID_ePro_ERROR"
            ElseIf MED_CLM_ROLL_IND = "R" Then
                fnM01Errors = "AUTO CLM_FWD_IND does not match MED_CLM_ROLL_IND"
            End If
        End If
    End If    

End Function

Upvotes: 1

Parfait
Parfait

Reputation: 107642

Reconsider a pure SQL solution with nested IIF(). Arguably it is not too overly complex just many logic clauses, plus you avoid type casting and adding another coding layer to setup:

SELECT
     ...
     IIF([AUTO_CLM_ID] = 'Y' 
         AND [MED_CLM_ROLL_IND] <> 'R'
         OR ([AUTO_CLM_ID] = 'N' OR [AUTO_CLM_ID] LIKE '*-*') 
         AND [MED_CLM_ROLL_IND] <> 'N', 
         'AUTO CLM_FWD_IND does not match MED_CLM_ROLL_IND', 
         IIF(CLM_FWD_EFF_DT IS NOT NULL 
             AND CLM_FWD_CAN_DT IS NOT NULL 
             AND CCF_PKG_TY = 'HRA' 
             AND CLM_FWD_APPLY_IND = 'Y' 
             AND MED_EFF_DATE >= CLM_FWD_CAN_DT 
             AND MED_CLM_ROLL_IND = 'N', 
             'No Error', 
             IIF(CLM_FWD_EFF_DT IS NOT NULL 
                 AND CLM_FWD_CAN_DT IS NOT NULL 
                 AND CCF_PKG_TY = 'HSA' 
                 AND CLM_FWD_APPLY_IND = 'Y' 
                 AND MED_EFF_DATE >= CLM_FWD_CAN_DT 
                 AND MED_CLM_ROLL_IND = 'N', 
                 'INVALID_ePro_ERROR',
                IIF(CLM_FWD_EFF_DT IS NOT NULL 
                    AND CLM_FWD_CAN_DT IS NOT NULL 
                    AND CCF_PKG_TY = 'HSA' 
                    AND CLM_FWD_APPLY_IND = 'Y' 
                    AND MED_EFF_DATE >= CLM_FWD_CAN_DT 
                    AND MED_CLM_ROLL_IND = 'R', 
                    'AUTO CLM_FWD_IND does not match MED_CLM_ROLL_IND', 
                    NULL)
             )
         )
     ) AS fnM01Errors

FROM myTable

Upvotes: 1

iDevlop
iDevlop

Reputation: 25262

You made a few mistakes:

  1. all nullable parameters must be defined as Variant.
  2. If must be closed with End If
  3. if x <> Null does not work. Use If not IsNull(x) Then instead.

You could also use the construct: 

If...then
ElseIf ...then
ElseIf...then
End if

Note that if your tables will have lots of data, an IIF() will be be much faster.

Upvotes: 3

Related Questions