Link to home
Start Free TrialLog in
Avatar of dgold5
dgold5Flag for United States of America

asked on

Access find nearest date after a given date

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?
Avatar of als315
Flag of Russian Federation image

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.
Avatar of dgold5


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.
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.
Avatar of dgold5


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.
Avatar of kmslogic
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dgold5


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.