MS Access Top 1 based on Date
Posted on 2014-09-09
I have a daily data set that has some data that is not daily - it might be weekly or bi-weekly or some other timeframe. For data that is not daily, I need to use the most recent value in all days following until I hit a day with a new value. I am trying to run the following query to carry a value from one day to the next if there isn't a value there for use in a daily calculation. When I run the following, I get an "Invalid Argument to Function Error"
SELECT qryA.sample_date, qryA.flow, iif(qryA.fds is not null, qryA.fds,(SELECT TOP 1 Dupe.fds
FROM qryA as Dupe
WHERE Dupe.sample_date < qryA.sample_date AND Dupe.FDS Is Not Null
ORDER BY Dupe.sample_date DESC)) AS fds_mod
WHERE (qryA.sample_date Between #4/1/2013# And #5/31/2013#);
If I change the "WHERE Dupe.sample_date < qryA.sample_date" to "WHERE Dupe.sample_date < #5/16/2013#" it works. I need to be able to run the select top 1 based on the current records date.