Hi I created a function that takes a single date as and input,
and outputs a string that I want to use in a query as search criteria. The string is a date range that comes out like this : Between #2023-04-17# AND #2023-04-21#

If I copy the function result right out of the immediate window  and paste is into the query grid it works as intended. But if I call the function from the query grid like this:
GetWorkWeekRange([TempVars![TVeD])
it either show no results, or gives an error. but it does successfully call the function because I can see the string in the immediate window.

the TempVars is created elsewhere but it feeds the function a date formated like this :

4/19/2023

The Function looks like this:

code

Public Function GetWorkWeekRange(inputDate As Date) As String

    Dim startDate As Date
    Dim endDate As Date
    Dim dayOfWeek As Integer
    dayOfWeek = Weekday(inputDate)
    If dayOfWeek = vbMonday Then
        'if the input date is a Monday, use the previous week's range
        startDate = inputDate - 6
        endDate = inputDate
    Else
        'otherwise, use the current week's range
        startDate = inputDate - dayOfWeek + 2
        endDate = startDate + 4
    End If
    GetWorkWeekRange = "Between #" & Format(startDate, "yyyy-mm-dd") & "# AND #" & Format(endDate, "yyyy-mm-dd" & "#")
    Debug.Print GetWorkWeekRange
    
End Function