Access function with NULL date values

0
votes

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?

3
vbams-accessms-access-2013
I dont know if this is whats causing the specific error you're getting, but you don't have "End If" after any of your If statements, (or ElseIf's for that matter) – DMcLaren
The query processor will send nulls, even sometimes when the query processor is starting up to get rows etc. In these cases nulls for every value is common. And a string data type parameter can't hold a null. Your use of nz() is just fine. Another way is to make all of the paramters of varient type, since they can hold null values. but, if you use variant for the parameter type, then in code, you have to use isNull(that param variable) = true, or false. So, I usually use variant types, since it then makes the sql use of the function somewhat less messy (no nz() needed). But either way is ok. – Albert D. Kallal

3 Answers

3
votes

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.

1
votes

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
1
votes

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