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
lancegallagher_expertsexchangeAsked:
Who is Participating?
 
mbizupCommented:
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

0
 
mbizupCommented:
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)
0
 
Rey Obrero (Capricorn1)Commented:
for the nextlower use dmax instead of dmin


varNextLower = DMax("Upload_Date", "tbl_Dates", "Upload_Date < #" & txtDate & "#")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mbizupCommented:
Yup - that was a copy/paste mistake in my post.
0
 
lancegallagher_expertsexchangeAuthor Commented:
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
0
 
mbizupCommented:
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)
0
 
lancegallagher_expertsexchangeAuthor Commented:
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?
0
 
mbizupCommented:
Is this a true Date field, or is it text?

And what format do the values appear in the table itself?
0
 
mbizupCommented:
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

0
 
lancegallagher_expertsexchangeAuthor Commented:
Hi,

The text box txtDate is format as short date . The format of Upload_Date is short date too.
0
 
lancegallagher_expertsexchangeAuthor Commented:
Great, that works a treat. Thanks very much for your help and quick response.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.