Finding record closest to a given date

  • Thread starter Thread starter Yair Sageev
  • Start date Start date
Y

Yair Sageev

Hi,

I am trying to pull up a record in a related table for a project whose date
is closest too a given date. I'm not sure how to construct a query to do
this.
 
Subtract one date from the other and then wrap it in Abs().
Abs([Date1]-[Date2])
Your query can select TOP 1.
 
Thanks, Duane. It's still not clear to me though.

The field in the form is DateEntry. Table X has N entries with DateRepair.

I want to run the query so that so that the record pulled from X has the
most recent date <=[Forms]![Projects]![DateEntry] .

From what you are saying, what would the criteria look like, exactly?




Duane Hookom said:
Subtract one date from the other and then wrap it in Abs().
Abs([Date1]-[Date2])
Your query can select TOP 1.

--
Duane Hookom
MS Access MVP


Yair Sageev said:
Hi,

I am trying to pull up a record in a related table for a project whose date
is closest too a given date. I'm not sure how to construct a query to do
this.
Click to expand...
Click to expand...
 
I didn't supply any criteria. Sort the query on the expression:
Abs([Forms]![Projects]![DateEntry]-[DateRepair])
and then return only one record using TOP 1 in the query properties.

--
Duane Hookom
MS Access MVP


Yair Sageev said:
Thanks, Duane. It's still not clear to me though.

The field in the form is DateEntry. Table X has N entries with DateRepair.

I want to run the query so that so that the record pulled from X has the
most recent date <=[Forms]![Projects]![DateEntry] .

From what you are saying, what would the criteria look like, exactly?




Duane Hookom said:
Subtract one date from the other and then wrap it in Abs().
Abs([Date1]-[Date2])
Your query can select TOP 1.

--
Duane Hookom
MS Access MVP


Yair Sageev said:
Hi,

I am trying to pull up a record in a related table for a project whose date
is closest too a given date. I'm not sure how to construct a query to do
this.
Click to expand...
Click to expand...
Click to expand...
 
Got it. Thanks.


Duane Hookom said:
I didn't supply any criteria. Sort the query on the expression:
Abs([Forms]![Projects]![DateEntry]-[DateRepair])
and then return only one record using TOP 1 in the query properties.

--
Duane Hookom
MS Access MVP


Yair Sageev said:
Thanks, Duane. It's still not clear to me though.

The field in the form is DateEntry. Table X has N entries with DateRepair.

I want to run the query so that so that the record pulled from X has the
most recent date <=[Forms]![Projects]![DateEntry] .

From what you are saying, what would the criteria look like, exactly?




Duane Hookom said:
Subtract one date from the other and then wrap it in Abs().
Abs([Date1]-[Date2])
Your query can select TOP 1.

--
Duane Hookom
MS Access MVP


Hi,

I am trying to pull up a record in a related table for a project whose
date
is closest too a given date. I'm not sure how to construct a query
Click to expand...
to
do
this.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top