asked on

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

mbizup

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)

for the nextlower use dmax instead of dmin

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

mbizup

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

lancegallagher_expertsexchange

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

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

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?

mbizup

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

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

mbizup

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

ASKER

Hi,

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

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lancegallagher_expertsexchange

ASKER

Great, that works a treat. Thanks very much for your help and quick response.