Access find nearest date after a given date

dgold5 used Ask the Experts™
I have a table consisting of ten years worth of pay period beginning and ending dates.  From an unbound date field on a form, I want to query and return the closest pay period ending date from the table.  Any suggestions?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you upload DB with sample data and expected result?
You can use dmax with filters, for example, but syntax is dependent on your table structure.
You could try something like this:

SELECT TOP 1 [PayEndingDt]
FROM tblPayPeriods
ORDER BY Abs(DateDiff("d",[YourDateGoesHere],[PayEndingDt]))

Of course you have to change tblPayPeriods and PayEndingDt to  your actual table name and field.
dgold5Owner of tax practice  Enrolled Agent (EA)


Snipping tool capture attached.  From an unbound date field on an input form, I'm trying to return the value of the table's EndDate field that is closest to but less than the date on the input form.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Using your table and field names:

SELECT TOP 1 [EndDate]
FROM tblPayDate_LU
ORDER BY Abs(DateDiff("d",[YourDateGoesHere],[EndDate]))
how about:

FROM tblPayDate_LU
WHERE EndDate < [DateFromYourForm]

You can use something like

DLookup("EndDate", "tblPayDate_LU", "StartDate<=#" & Forms!YourForm!YourDate & "# AND EndDate >=#" & Forms!YourForm!YourDate & "#")
I didn't see your corrected criteria about the closest ending date being less in your 2nd post.  So either smilitaru's or kmslogic answer should work for you.
dgold5Owner of tax practice  Enrolled Agent (EA)


Thanks to everyone who responded so promptly to my question.  I still don't know if I have a solution or not because I always tend to get bollixed up in SQL/VBA syntax and storing the result into a variable I can then work further with.  I haven't been able to spend much time with it until today but I'm still not there.   I do know that the aggregate function submitted by IrogSinta doesn't produce the expected result, although it would be the easiest methodology for me to implement.   The expected result would be 7/14/2012 for any specific date entered from 7/1/2012 - 7/14/2012  (based on the attached table).  And yes - I'll have an edit that precludes any specific data entry that is a Saturday, Sunday, or Holiday.

I'm going to try to explain what I need in a more functional way:  The result of user input into the attached form is a list of personnel who are scheduled  to be on AWS (Alternate Work Schedule) and/or Teleworking for any given work day.  There is a separate table that stores duty hours and location (Ofc, AWS, Tele) by employee for each day of the two-week payroll period ( Week 1 or 2, Mon-Fri).  You can see from the user form that they can specify a particular day of either week of a pay period, or a specific date.  I can do everything except translate a specific date to a corresponding day of the payroll period.  Once I know the correct ending date of the pay period I can work backwards to find the correct week and day to look up.  It's finding the end date of the pay period from a specifid date that is what I haven't  been able to accomplish.  I think I asked the correct question before, but maybe this will shed more light - or maybe I just have to struggle through the syntax.   That's what I'm trying to accomplish.
I'm pretty sure that dlookup() I gave will do the trick...  To assign to a variable:

MyVariable = DLookup("EndDate", "tblPayDate_LU", "StartDate<=#" & Forms!YourForm!YourDate & "# AND EndDate >=#" & Forms!YourForm!YourDate & "#")

a NULL return means it didn't find any records that matched the criteria. You can check for this with

IF IsNull(MyVariable) Then
   msgbox "Oh man, it's null."
End If
dgold5Owner of tax practice  Enrolled Agent (EA)


Thanks so much - this works fine - the simplest and easiest solution to implement.  It actually worked fine the first time around, but I don't even want to tell you what a stupid mistake I made in my look up table that was confusing the issue.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial