Results 1 to 3 of 3
  1. #1
    FrustratedAlso is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Apr 2012
    Posts
    35

    Question Function returning Error 91 (can't Figure out)

    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

  2. #2
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    I may be missing something but I believe you missed opening your recordset, you defined the SQL string and then did nothing with it.
    The only thing you did with salrec was salrec.close
    (I'm no expert on this by any means I'm still teaching myself this at the moment)
    Last edited by R_Badger; 04-17-2012 at 03:25 AM. Reason: Corrected my terrible spelling!

  3. #3
    FrustratedAlso is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Apr 2012
    Posts
    35
    You are absolutely right! I said I had stared at it too long Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Function - Returning a Value
    By dreamnauta in forum Programming
    Replies: 3
    Last Post: 01-05-2012, 03:37 PM
  2. Blank Field returning #Error
    By imintrouble in forum Access
    Replies: 8
    Last Post: 12-09-2011, 03:00 PM
  3. Having Trouble Returning Array from Function
    By NigelS in forum Programming
    Replies: 8
    Last Post: 08-15-2011, 07:12 AM
  4. IIF Statement Returning #Error
    By DrDefpoints in forum Queries
    Replies: 6
    Last Post: 05-26-2011, 12:25 PM
  5. Calling a function and returning a value
    By 3dmgirl in forum Programming
    Replies: 0
    Last Post: 04-23-2007, 02:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums