I am running a make-table query which needs to find a date that matches the input date or find the nearest within the last 180 days.
SELECT DISTINCTROW TPData.[Date Received], TPData.ID, TPData.[Specimen No], patient.forename, patient.surname, episode.admitdate, episode.dischdate, patientdod, episode.cc, episode.place, episode.wcode INTO Patient_July2004
FROM ((episode INNER JOIN numberlinks ON episode.link = numberlinks.link) INNER JOIN patient ON episode.link = patient.link) INNER JOIN TPData ON numberlinks.number = TPData.ID
WHERE (((TPData.[Date Received]) Between #6/1/2004# And #6/30/2004#) AND ((episode.admitdate)<=[Date Received] And (episode.admitdate)>([Date Received]-180)) AND ((episode.place)="Home1" Or (episode.place)="Home2") AND ((numberlinks.Homelink)=1))
ORDER BY TPData.[Specimen No], episode.admitdate DESC;
When I run this I get the dates that are 180 days old not the one that is nearest to the date received.
How can I run the query so I can get the date nearest to the date received? The date is always before the date received.