Date() Function is Date Time?

[BikeToWork]

I thought that the Date() function did not have a time component and that if you say <Date() as a criteria, it will only include records before today's date. What I'm trying to do is pull a field value for the most recent date (less than today's date) from a table with multiple dates. If I try

SELECT Max(tblUserCount.CurrentDate) AS MaxOfCurrentDate
FROM tblUserCount
HAVING (((Max(tblUserCount.CurrentDate))<Date()));

There are no results even though there are records in tblUserCount with yesterday's date. Please advise.

[Rabbit]

That's because you're using the HAVING clause which is a filter that takes place after the aggregates are calculated. So it first takes the max date from all the records, then checks to see if the max date of all the records are before today's date. Which will never be true if you have any records with today's date.

[BikeToWork]

Thanks, Rabbit. That makes sense, however I cannot use an aggregate function in a where clause, so is there some workaround? I'm just trying to find the maximum date in the data that is less than today. That may be yesterday or it may be a few days ago. Any advice. Thanks for your help.

[Rabbit]

You don't need an aggregate in the where clause, there's no need to take the max for your filter.

[BikeToWork]

I think I need two queries then, one for the Date() value and another for the max date less than the Date() value in the first query. Thank again for your help.

[Rabbit]

You only need one query.

SELECT Max(tblUserCount.CurrentDate) AS MaxOfCurrentDate
FROM tblUserCount
WHERE tblUserCount.CurrentDate<Date();