Query to assign values to a cost item based on dates from a contract (1 Viewer)

Public Function fnContractNum(dDate As Variant, ctype As Variant, location As Variant, vendor As Variant) As Variant
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim str As String
    dDate = CDate(dDate)
    
    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("SELECT ContractNumber From [Contract List] Where " & _
                    "#" & Format(dDate, "mm/dd/yyyy") & "# Between " & _
                    "ValidFrom And Nz(ValidTo, #" & _
                    Format(DateSerial(Year(Date) + 1, 12, 31), "mm/dd/yyyy") & "#) And " & _
                    "VendorID = " & FixUp(vendor), _
                    dbOpenSnapshot)
    If RS.RecordCount = 1 Then
        fnContractNum = RS(0)
    Else
        If RS.RecordCount > 0 Then
            With RS
                .MoveFirst
                While Not .EOF
                    str = str & !contractnumber & ","
                    .MoveNext
                Wend
                str = Left(str, Len(str) - 1)
                .Close
            End With
            Set RS = DB.OpenRecordset("select ContractNumber, Location from [Contract Assignment] Where " & _
                "ContractNumber IN (" & str & ");", dbOpenSnapshot)
            With RS
                If Not (.BOF And .EOF) Then
                    .MoveFirst
                    Do While Not .EOF
                        If Trim(!location & "") = location & "" Then
                            fnContractNum = !contractnumber
                            Exit Do
                        End If
                    Loop
                End If
                .Close
            End With
        End If
    End If
    Set RS = Nothing
End Function


Public Function FixUp(ByVal varvalue As Variant) As Variant

    ' eTutorials.org
    ' SQLQuote
    ' Add appropriate delimiters, depending on the datatype.
    ' Put quotes around text, #s around date, and nothing
    ' around numeric values.
    'Const QUOTE As String = Chr$(34)
    Dim QUOTE As String
    QUOTE = Chr$(34)
    
    Select Case VarType(varvalue)
        Case vbInteger, vbSingle, vbDouble, vbLong, vbCurrency, vbBoolean, vbByte
            FixUp = CStr("0" & varvalue)
        
        Case vbString
            FixUp = QUOTE & varvalue & QUOTE
            
        Case vbDate
            FixUp = "#" & Format(varvalue, "mm/dd/yyyy") & "#"
            
        Case Else
            FixUp = "Null"
    End Select
End Function