MS-Access find closest date

Hello experts, I have a tricky problem with trying to find the closest date that I need some help with.

When a user enters a date in a text box, I would like to find the nearest date in a table, the closest future date if available otherwise the closest past date

e.g if the text box value is 29-Feb-2012 then I would like to return the value 02-Mar-2012 from the table [TBL_Dates].[Upload_Date] below.

[TBL_Dates].[Upload_Date]
17-Feb-2012
24-Feb-2012
02-Mar-2012
09-Mar-2012

Thanks for any help

Microsoft Access

Last Comment

lancegallagher_expertsexchange

8/22/2022 - Mon

mbizup

3/14/2012

You can use a VBA function -- this assumes that your table is populated (not empty):

Dim varNextHigher as Variant
Dim varNextLower as Variant

varNextHigher = DMin("Upload_Date", "tbl_Dates", "Upload_Date > #" & txtDate & "#")
varNextLower = DMin("Upload_Date", "tbl_Dates", "Upload_Date < #" & txtDate & "#")

msgbox "Closest Date = " & Nz(varNextHigher,NextLower)

Rey Obrero (Capricorn1)

3/14/2012

for the nextlower use dmax instead of dmin

varNextLower = DMax("Upload_Date", "tbl_Dates", "Upload_Date < #" & txtDate & "#")

mbizup

3/14/2012

Yup - that was a copy/paste mistake in my post.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

lancegallagher_expertsexchange

3/14/2012

ASKER

Hi, having trouble getting it to work for me

I find that I have to enter the date in American date format

e.g if I enter 08/03/2012 and run the procedure I get closest date =

however if I enter 03/08/2012 and run the procedure I get closest date = 09/03/2012

mbizup

3/14/2012

Try this:

Dim varNextHigher as Variant
Dim varNextLower as Variant

varNextHigher = DMin("Upload_Date", "tbl_Dates", "Upload_Date > #" & cDate(txtDate) & "#")
varNextLower = DMax("Upload_Date", "tbl_Dates", "Upload_Date < #" & CDate(txtDate) & "#")

msgbox "Closest Date = " & Nz(varNextHigher,NextLower)

lancegallagher_expertsexchange

3/14/2012

ASKER

Hi, still the same problem when using

varNextHigher = DMin("Upload_Date", "tbl_Dates", "Upload_Date > #" & cDate(txtDate) & "#")
varNextLower = DMax("Upload_Date", "tbl_Dates", "Upload_Date < #" & CDate(txtDate) & "#")

what do you think I should try next?

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

mbizup

3/14/2012

Is this a true Date field, or is it text?

And what format do the values appear in the table itself?

mbizup

3/14/2012

You can also try formatting the dates identically:

varNextHigher = DMin("Upload_Date", "tbl_Dates", "Format('yyyy-mm-dd', Upload_Date) > '" & Format("yyyy-mm-dd", (txtDate) & "'")
varNextLower = DMax("Upload_Date", "tbl_Dates", "Format('yyyy-mm-dd', Upload_Date) < '" & Format("yyyy-mm-dd", (txtDate) & "'")

Open in new window

lancegallagher_expertsexchange

3/14/2012

ASKER

Hi,

The text box txtDate is format as short date . The format of Upload_Date is short date too.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

ASKER CERTIFIED SOLUTION

mbizup

3/14/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.