Perhaps I've stared at this too long but I can't figure out the error
Attachment 7200
Code:
Public Function MthYr(rNbr As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return just the month/Year from a Date.
'Return: MM/YYYY, or Null if no matches.
'Arguments: rNbr = Inventory Key to get results from Sales.
'
'Notes: 1. Use square brackets around field/table names with spaces or odd characters.
' 2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
' 3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
' 4. Returning more than 255 characters to a recordset triggers this Access bug:
' http://allenbrowne.com/bug-16.html
Dim SalRec As DAO.Recordset 'Related records
Dim strSql As String 'SQL statement
Dim strOut As Date 'Output Variable.
Dim SalDte As Date
'Initialize to Null
MthYr = Null
'Build SQL string, and get the records.
strSql = "SELECT Sales.[InventoryNumber], Sales.[SaleDate] From Sales where Sales.[InventoryNumber] = '" & rNbr & "'"
' Check for Sale
If Not IsNull(SalRec(1)) Then
SalDte = SalRec(1)
strOut = Month(SalDte) & "/" & Year(SalDte)
End If
SalRec.Close
'Return the result
MthYr = strOut
Exit_Handler:
'Clean up
Set SalRec = Nothing
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "MthYr()"
Resume Exit_Handler
End Function