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.