Avatar of dgold5
Flag 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?
Microsoft AccessMicrosoft ApplicationsSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon

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.

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.
Your help has saved me hundreds of hours of internet surfing.

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 & "#")
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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.

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.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck