Results 1 to 6 of 6
  1. #1
    ForeverNoob is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    3

    Custom Function to find Closest Date


    I have a table of Employee Labour Account changes with dates when the change happened. On the table holding the punch card info there is the date they worked. What I'm trying to create is a custom function to use in a query where I take their employee ID and the date worked, then looking in the account table find their record that is the closest (but not newer) date to their worked date so that I can link up the correct account to that work day for them. I've attempted the following, however, I am new to Access VBA :

    Code:
    Public Function GetCloseDate(EmpNum As Double, SpanDate As Date) As Date
    
    
    Dim r As Double
    Dim GCD As Double
    Dim EmpDate As Date
    
    
    GetCloseDate = CDate("1/1/2000")
    GCD = 36526 '1/1/2000
    
    
    While [EmployeeAccountsSplit].[ID] = EmpNum
        EmpDate = [EmployeeAccountsSplit].[Effective Date]
        r = DateDiff("d", EmpDate, SpanDate)
        If r < GCD Then
            GCD = r
        End If
    Wend
        GetCloseDate = CDate(r)
    End Function
    When I attempt to use this it errors out on the While line stating object required. I've been trying to google how to properly reference a table and column in Access but it seems very confusing. Any help would really be appreciated.

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Maybe something like this:
    Code:
    Public Function GetCloseDate(EmpNum As Double, SpanDate As Date) As Variant
    On Error GoTo ErrHandler_GetCloseDate
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim rslt As Variant
    
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT TOP 1 [date field] FROM [account table] WHERE EmpNum=" & EmpNum & " AND [date field]<#" & SpanDate & "# ORDER BY [date field] DESC;")
        If Not (rs.BOF And rs.EOF) Then
            'Query found a record
            rslt = rs![date field]
        Else
            'Query didn't find anything
            'put your code here if the query didn't find anything
            rslt = Null
        End If
        rs.Close
    
    ExitHandler_GetCloseDate:
        Set rs = Nothing
        Set db = Nothing
        
        GetCloseDate = rslt
        
        Exit Function
    
    ErrHandler_GetCloseDate:
        MsgBox Err.Description, vbInformation, "GetCloseDate: Error #" & Err.Number
        Resume ExitHandler_GetCloseDate
    End Function

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'd use Do While, not While, but perhaps you know something about your syntax that I'm not familiar with. Regardless, I think Access has a problem with your table.field reference; i.e. it needs to point to a recordset, query or a form/report. For example, if on a form then Me would be the object.

    Did you consider using a subquery for this?
    http://allenbrowne.com/subquery-01.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ForeverNoob is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    3
    Hi kd,
    I added my table and column names into your code like so :

    Code:
    Public Function GetCloseDate(EmpNum As Double, SpanDate As Date) As VariantOn Error GoTo ErrHandler_GetCloseDate
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim rslt As Variant
    
    
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT TOP 1 [Effective Date] FROM [tblEmployeeAccts] WHERE EmpNum=" & EmpNum & " AND [Effective Date]<#" & SpanDate & "# ORDER BY [Effective Date] DESC;")
        If Not (rs.BOF And rs.EOF) Then
            'Query found a record
            rslt = rs![Effective Date]
        Else
            'Query didn't find anything
            'put your code here if the query didn't find anything
            rslt = Null
        End If
        rs.Close
    
    
    ExitHandler_GetCloseDate:
        Set rs = Nothing
        Set db = Nothing
        
        GetCloseDate = rslt
        
        Exit Function
    
    
    ErrHandler_GetCloseDate:
        MsgBox Err.Description, vbInformation, "GetCloseDate: Error #" & Err.Number
        Resume ExitHandler_GetCloseDate
    End Function
    However, now I'm getting the error : "Run-time error '3061'. Too few parameters. Expected 1." My formula looks like : GetCloseDate([tblSpans].[ID],[tblSpans].[Apply Date])

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Double check the query to make sure all of these red parts match up exactly with your table and field names. Maybe it's EmpNum?

    Code:
    Set rs = db.OpenRecordset("SELECT TOP 1 [Effective Date] FROM [tblEmployeeAccts] WHERE EmpNum=" & EmpNum & " AND [Effective Date]<#" & SpanDate & "# ORDER BY [Effective Date] DESC;")
    Also, should the employee number really be a double?

  6. #6
    ForeverNoob is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    3
    Thank you kd, I corrected that row as below and it now works :

    Code:
    Set rs = db.OpenRecordset("SELECT TOP 1 [Effective Date] FROM [tblEmployeeAccts] WHERE [ID]=" & EmpNum & " AND [Effective Date]<#" & SpanDate & "# ORDER BY [Effective Date] DESC;")

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

Similar Threads

  1. Find Closest Lowest Number
    By koturtle in forum Queries
    Replies: 9
    Last Post: 03-31-2020, 02:57 PM
  2. Replies: 12
    Last Post: 05-02-2017, 07:39 PM
  3. Replies: 2
    Last Post: 09-03-2015, 04:38 PM
  4. Find Variables closest to specified Date
    By crimedog in forum Reports
    Replies: 1
    Last Post: 01-30-2014, 11:10 AM
  5. find the closest year
    By 12345678 in forum Queries
    Replies: 1
    Last Post: 05-25-2011, 05:07 AM

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