Don't see what those queries have to do with functions and the issue I identified. Do you understand what I described?
What change did you make in code? Did you add criteria for date range?
here is last code
Public Function IsTransferValid(DocNumber As Long, Transfer As String, DateOfReceiving As Date) As Boolean
On Error GoTo HandleError
Dim parts() As String
Dim startDoc As Long, endDoc As Long
Dim thisYear As Long
Dim rs As DAO.Recordset
Dim dateStart As Date, dateEnd As Date
Dim sql As String
Debug.Print "---- Start Check ----"
Debug.Print "DocNumber: " & DocNumber
Debug.Print "Transfer: " & Transfer
Debug.Print "DateOfReceiving: " & DateOfReceiving
If Trim(Transfer) = "" Or UCase(Transfer) = "N/A" Then
Debug.Print "Transfer is empty or N/A"
IsTransferValid = True
Exit Function
End If
If InStr(Transfer, "/") = 0 Then
Debug.Print "No slash found in Transfer"
IsTransferValid = False
Exit Function
End If
parts = Split(Transfer, "/")
If UBound(parts) <> 1 Then
Debug.Print "Transfer not in expected format (X/Y)"
IsTransferValid = False
Exit Function
End If
If Not IsNumeric(parts(0)) Or Not IsNumeric(parts(1)) Then
Debug.Print "One of the parts is not numeric"
IsTransferValid = False
Exit Function
End If
startDoc = CLng(parts(0))
endDoc = CLng(parts(1))
Debug.Print "StartDoc: " & startDoc & ", EndDoc: " & endDoc
If endDoc <> startDoc + 1 Then
Debug.Print "EndDoc is not StartDoc + 1"
IsTransferValid = False
Exit Function
End If
thisYear = Year(DateOfReceiving)
Debug.Print "Year of receiving: " & thisYear
sql = "SELECT COUNT(*) AS C FROM tblDocuments WHERE DocNumber IN (" & startDoc & "," & endDoc & ") AND Year(DateOfReceiving)=" & thisYear
Set rs = CurrentDb.OpenRecordset(sql)
Debug.Print "Boundary records found: " & rs!C
If rs!C < 2 Then
Debug.Print "One or both boundary documents missing"
IsTransferValid = False
rs.Close
Exit Function
End If
rs.Close
sql = "SELECT DateOfReceiving FROM tblDocuments WHERE DocNumber=" & startDoc & " AND Year(DateOfReceiving)=" & thisYear
Set rs = CurrentDb.OpenRecordset(sql)
If rs.EOF Then
Debug.Print "StartDoc date not found"
IsTransferValid = False
rs.Close
Exit Function
End If
dateStart = rs!DateOfReceiving
Debug.Print "Start Date: " & dateStart
rs.Close
sql = "SELECT DateOfReceiving FROM tblDocuments WHERE DocNumber=" & endDoc & " AND Year(DateOfReceiving)=" & thisYear
Set rs = CurrentDb.OpenRecordset(sql)
If rs.EOF Then
Debug.Print "EndDoc date not found"
IsTransferValid = False
rs.Close
Exit Function
End If
dateEnd = rs!DateOfReceiving
Debug.Print "End Date: " & dateEnd
rs.Close
sql = "SELECT 1 FROM tblDocuments " & _
"WHERE DocNumber = " & DocNumber & _
" AND Year(DateOfReceiving) = " & thisYear & _
" AND DateOfReceiving > #" & Format(dateStart, "mm/dd/yyyy") & "# " & _
" AND DateOfReceiving < #" & Format(dateEnd, "mm/dd/yyyy") & "#"
Debug.Print "Check range SQL: " & sql
Set rs = CurrentDb.OpenRecordset(sql)
Debug.Print "Record found between dates: " & Not rs.EOF
IsTransferValid = Not rs.EOF
rs.Close
Debug.Print "Result: " & IsTransferValid
Debug.Print "---- End Check ----"
Exit Function
HandleError:
Debug.Print "ERROR: " & Err.Description
IsTransferValid = False
End Function
