VBA funcion(fnSpecialDate) causes inability to sort / filter on result

[Scaniafan]

Hello all,

I'm working with a function created by Arnelgp to add specific holidays on freight forwarder country to a source date.

Public Function fnGetHolidays(varCountryCode As Variant, dDate As Variant) As Variant

    Dim strSql As String
    Dim rs As DAO.Recordset
    Dim strCountryCode As String
    Dim arrHolidays() As Variant
        
    varCountryCode = Trim(varCountryCode & "")
    dDate = CDate(dDate)
    If varCountryCode <> "" Then
    
        If db Is Nothing Then Set db = CurrentDb
        
        strSql = "Select [Date] From [TBL_Source_Holidays] " & _
                        "Where [Country Code] = '" & varCountryCode & "' " & _
                        "And Year(#" & dDate & "#) >= Year([Date]);"
        Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
            
        With rs
            If .RecordCount > 0 Then
                .MoveLast
                ReDim arrHolidays(0) As Variant
                .MoveFirst
                While Not .EOF
                    arrHolidays(UBound(arrHolidays)) = CDate(![Date].Value)
                    .MoveNext
                    If Not .EOF Then ReDim Preserve arrHolidays(UBound(arrHolidays) + 1) As Variant
                Wend
            End If
        End With
        
        fnGetHolidays = arrHolidays
    
    End If
    
End Function

Public Function fnSpecialDate(lngCount As Long, varCountryCode As Variant, Optional dDate As Date = 0) As Date
'Test: dhAddWorkDaysA(2,[Date],fnGetHolidays([TBL_Master_CP201]![Forwarding agent]))
Dim arrDates() As Variant
arrDates = fnGetHolidays(varCountryCode, dDate)
fnSpecialDate = CDate(dhAddWorkDaysA(lngCount, dDate, arrDates))

End Function

Simple explanation, if the freight forwarder is based in the US, and the target date (source date + x number of days) is 4th of July, the target date moves to the 5th of July. This part works like a charm.

However, being several steps further down the line in creating my reporting, I come to the conclusion that I cannot filter on this date. If I use Between #dd-mm-yyyy# And #dd-mm-yyyy#, I get the error message:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

If I try to sort the field with the results, I the error message

Data type mismatch in criteria expression

What I've tried until now:

- Use CDate() around the expression > results in "Invalid use of Null"

- Create a second query with the expression Test: CDate([target date field]) > results in "Invalid use of Null"

- Put the format property of the output field to short date > "Data type mismatch in criteria expression."

What can be the reason for this? It only occurs on the fields where I use the fnSpecialDate in the expression, all other date fields are not causing any problem


--- Edit ---

If I run the query as a make-table query, I am able to filter the results in the table as I want...

 

[gemma-the-husky]

if possible, one way is to add another field in your table, and set the destinationdate using the function as a one-off exercise. then you have the date without further evaluation.

this works fine as long as it isn't changing all the while. One instance where it is worth storing a calculated value

 

[Scaniafan]

Hm, that is an option, since the date is being determined by the moment a shipment has been delivered.

Thanks, will investigate that workaround!

-----

Workaround works