We help IT Professionals succeed at work.

MS-Access find closest date

Medium Priority
591 Views
Last Modified: 2012-03-14
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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)
CERTIFIED EXPERT
Top Expert 2016

Commented:
for the nextlower use dmax instead of dmin


varNextLower = DMax("Upload_Date", "tbl_Dates", "Upload_Date < #" & txtDate & "#")
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Yup - that was a copy/paste mistake in my post.
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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)
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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

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

And what format do the values appear in the table itself?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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

Hi,

The text box txtDate is format as short date . The format of Upload_Date is short date too.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Sorry- I've got the dates and format strings backwards;

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

Open in new window

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