Display configuration based on date

[Guest]

I am creating a maintenance log which will show different parts and its date
installed. It is possible that the same part is changed so the log table
would have the same part name more than one time with different installation
dates. I want to create a query which will enable me to pick a date and see
the configuration on that date, ergo a query which chooses from 'duplicate'
records. To pick a date, the code in criteria i am using is "Between
#1/1/1990# AND [Enter a date:]" which has the user select the day,
essentially searching for all records between those dates. I then want the
query to inspect those entries, and for the duplicate ones, pick the one with
the installation date closest to the user's input for "enter a date".

The 'last' function does not work as it is possible someone will enter a
date for a part that is before the date of installation of another entry for
that same part i.e. the last function goes on chronological order, when the
entry was physically made to the database and not by the value in the date
field. I tried max, but if the maximum value of that field is not within the
selected range of dates, it discards all the rows which are duplicates, which
isn't good.

I am just about out of ideas. Any help anyone???

Tim

 

[John Spencer (MVP)]

Well, it is a bit hard to say, but if you are going to limit your query by date,
I would suggest you don't use between, but use Less than equal to[Enter a Date]
to reduce the number of parts records to just those on or before the cutoff
date. Then use MAX to get the date closest to (but less than) the cutoff date.

 

[Guest]

I tried this and I still get the same problem, if the Max date isnt <= the
entered date, all the entries for that part are discarded.

I know there's got to be a way to do it, but how??