Query

N

NL

How can I filter a record with more than one date to show me ONLY the closest
date to today.

Thank you.
 
J

John Vinson

How can I filter a record with more than one date to show me ONLY the closest
date to today.

Thank you.
Click to expand...

Put a calculated field in the Query by typing

DaysToService: Abs(DateDiff("d", [ServiceDate], Date()))

in a vacant Field cell. (You say "nearest" so I'm using the absolute
value function Abs(), so it will find the nearest past or future
date).

Sort by this field, descending, and set the Top Values property of the
query to 1 to show only the one date. If there are several records all
equally close you'll see all of them.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top