find the closest date 22/nov/2009

[chopo1980]

i have to find the closest date in a table ( query ) 3 tables

sale table

22/nov/2009

can anyone help me to find the closest date to..

thank you for time and interest.

 

[wilpeter]

In a query, create a field "Difference" that compares the parameter field with the Date field in the table. e.g.,
Difference: DateDiff("d",[match date],[Date])
Sort in Descending order

When run, the parameter will request the date (enter 22-nov-09) and result is in plus or minus days.

 

[Galaxiom]

Wilpeter's solution will order the difference but the closest date before will be a negative result so will be separate from the closest date after. Use the Abs function to ignore the sign of the result.

Difference: Abs(DateDiff("d",[match date],[SaleDate]))

Once you have the field, find the least difference make a textbox in the header or footer of the form witht he control source:
Min([Difference])

(Best avoid using Date as a fieldname as it is a reserved word.)

 

[wilpeter]

Thanks Galaxiom for fixing my attempt.

 

[gemma-the-husky]

if you want to find, say the closest date AFTER the target date, you can use DMIN

so something like

dmin(datefield,tablename, datefield >= targetdate)

will find the nearest date, greater than or equal to the target date

 

[wilpeter]

Gemma: I've never used the DMin function outside Excel. Is it used as an expression in the criteria row or as the definition of a new field? I tried it with the targetdate as a parameter without success, so my syntax must be in error.
Result: DMin([Datefield],[TblSALE],[Datefield]>=[Match date])
doesn't work as a calculated field.
=DMin([Datefield],[TblSALE],[Datefield]>=[Match date])
also doesn't work (since it doesn't recognize my Table Name as the domain).
Would you mine elucidating? Thanks.

 

[Galaxiom]

The syntax of the Domain functions requires quoted strings as the arguments.

DMin("fieldname","table/queryname", "something = anotherthing")

 

[wilpeter]

Thanks, Galaxiom, that's what I'm using without success (won't recognize my tablename--it prompts query for a value for it). I'm wondering if Gemma meant to use it as a calculated field expression or a criteria row. I'm not using code...and version 2003.

 

[Galaxiom]

Did you include the quote marks correctly? I don't think Access would prompt for a object when it is just in a string.
Domain functions can be used anywhere in Access. (They don't work in SQL Server.)

 

[boblarson]

Just to go with Galaxiom's post:

=DMin([Datefield],[TblSALE],[Datefield]>=[Match date])

SHOULD BE:

=DMin("[Datefield]","TblSALE","[Datefield]>=[Match date]")

 

[wilpeter]

Thanks guys, but (since [Match date] is a parameter for which I want a dialog prompt) I get an error message whether I use this expression as a criteria to the Datefield column of the query - or as the calculation of a new field.
"The expression you entered as a query parameter produced this error: 'Microsoft Access can't find the name 'Match date' you entered in the expression' "

My query has one table TblSALE and two fields, Name and Datefield. In the parameter would be inserted the date Nov 22, 2009 if it would prompt for it instead of pointing out it hasn't yet created it.

Are we having fun yet?

 

[boblarson]

if it would prompt for it instead of pointing out it hasn't yet created it.

Try using [Enter Match Date] as the criteria in the query (or do as I do and prefer - use a FORM to enter the parameters, which is better anyway as the user doesn't have to constantly enter information over and over again if they need it again or makes a mistake entering.

 

[wilpeter]

Same result. I created a form InputForm with a single unbound field Matchdate. I entered 22-Nov-2009 and left it open. I changed the expression in the criteria row of the Datefield in query to: =DMin("[Datefield]","TblSALE","[Datefield]>=[InputForm]![Matchdate]")

And Yes, I had saved the form. The message is 'The expression you entered as a query parameter produced this error: 'Microsoft Access can't find the name 'InputForm!Matchdate' you entered in the expression'

 

[boblarson]

Same result. I created a form InputForm with a single unbound field Matchdate. I entered 22-Nov-2009 and left it open. I changed the expression in the criteria row of the Datefield in query to: =DMin("[Datefield]","TblSALE","[Datefield]>=[InputForm]![Matchdate]")

Try this:


=DMin("[Datefield]","TblSALE","[Datefield]>=#" & [Forms]![InputForm]![Matchdate] & "#")

And if that doesn't work you may need a US formatted date:

=DMin("[Datefield]","TblSALE","[Datefield]>=#" & Format([Forms]![InputForm]![Matchdate], "mm\/dd\/yyyy") & "#")

 

[Vassago]

Try using [Enter Match Date] as the criteria in the query (or do as I do and prefer - use a FORM to enter the parameters, which is better anyway as the user doesn't have to constantly enter information over and over again if they need it again or makes a mistake entering.

It should actually be:

=DMin("[pulldate]","tbl_schedule_adherence","[Datefield]">=+"#" & [Match date]+"#")

Since [Match Date] is something you wanted entered, not already included in the expression, so you must not include it in the quotes. You also must add the # signs if you want it to be a true date/time value, since the expression won't recognize it otherwise. Give that a try.

 

[Vassago]

Dang Bob, you beat me to it...

 

[Galaxiom]

Declare the parameter.
At the beginning of the SQL add

PARAMETERS [Enter Match Date] DateTime

 

[wilpeter]

Thanks for your patience. Yes, the Forms! was indeed the missing link. Still, that leaves the fact that the parameter query method isn't working for me. Hopefully the original poster hasn't died in the meantime and will see the result that worked.

 

[Galaxiom]

Things moved on while I wasn't looking!

BTW another tidy way to express dates for sql is:
Format([datefield], "\#mm/dd/yyyy\#")

 

[wilpeter]

Sorry, Galaxiom, but I'm not using SQL. Old fashioned, I guess. Thanks, though.