Search results

I think I've decided on the simplest of the ingenious offers made. A calculated field that uses a parameter, which provides the degree of difference in days between the Datefield records and the variable date (22 Nov 2009) and limits the output to the top 3 results): Difference...

I'll give it a try tomorrow. I usually only use SQL for union queries and other complicated ones. This seemed like a simple expectation, even of 2003.

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

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.

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...

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...

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.

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...

Thanks Galaxiom for fixing my attempt.

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.

Just a question to this poster: In your original Relationship, did you set the join type to either 2 or 3 so that All records from Contacts table and just those that exist in the supporting table appear? Alternatively, did you try creating a query from those tables and set the Join Type...

Forgive me for asking, but If this phase is simply data building, can you trust yourself to enter records directly into the table (or are there multiple tables being updated by that form?) through the Datasheet view? Then you can use Ctrl+" to duplicate the above record's field values.