Finding record closest to a given date

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

 

[Duane Hookom]

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

 

[Yair Sageev]

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?

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 to do
this.

 

[Duane Hookom]

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

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?

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 to do
this.

 

[Yair Sageev]

Got it. Thanks.

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

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?

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

to
do

this.