Link to home
Start Free TrialLog in
Avatar of LillyC
LillyCFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How to get the closest date in a query in Access 2010

I have two dates in my query, 'Entitlement Start Date' and 'Start Date' and would like to identify the closest date.

Can anyone advice on how I could do this please?
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

use DateDiff("d", [Entitlement Start Date], [Start Date])
ASKER CERTIFIED SOLUTION
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LillyC

ASKER

Yes closest to todays date - its chucking out an error on the syntax but I'll work it out - thank you :)
Your welcome. Just in case you didn't notice, I updated the syntax of my first post after a quick test on my own system.
Avatar of LillyC

ASKER

Oh thanks, I've just recopied but still getting a syntax error
try changing ; with comma ,
Does it just say syntax error or something more detailed? You might need to replace the semicolons with commas (Semicolons come from my regional settings)
IIf(Abs(DateDiff("d",[Entitlement Start Date],Date()))>Abs(DateDiff("d",[Start Date],Date())),[Start Date],[Entitlement Start Date])

Open in new window

Avatar of LillyC

ASKER

Perfect... its worked, thank you both :)