Hi all.
Hopefully a simple one for any SQL guru. In an Access query, how can I group by a date field, without having my data broken down by time? In other words, I just want a count per day, not per second or whatever.
I'm using Access 2003, but I doubt it matters in this case.
At present I am just using a function (see below) to strip off the date, but I consider this terribly inefficient.
I did try some searching, but this is one case where there's too much info available. Just within TSDN there are dozens upon dozens of postings mentioning grouping by date, but they seem to mostly relate to reasons why or why not, different situations where one should group by dates, and so on.
What I've tried so far:
Hopefully a simple one for any SQL guru. In an Access query, how can I group by a date field, without having my data broken down by time? In other words, I just want a count per day, not per second or whatever.
I'm using Access 2003, but I doubt it matters in this case.
At present I am just using a function (see below) to strip off the date, but I consider this terribly inefficient.
I did try some searching, but this is one case where there's too much info available. Just within TSDN there are dozens upon dozens of postings mentioning grouping by date, but they seem to mostly relate to reasons why or why not, different situations where one should group by dates, and so on.
What I've tried so far:
- First I tried setting the GROUP BY column to Format(DateFiel d,"dd/mm/yyyy") and this worked, but obviously sorted in the wrong sequence.
- Currently my GROUP BY column is set to DateOnly(DateFi eld), and I have written...
Code:Public Function [B]DateOnly[/B](ByVal pDateTime As Date) As Date ' Simple function to strip off the time, leaving only the date. DateOnly = DateSerial(Year(pDateTime), Month(pDateTime), Day(pDateTime)) End Function
Comment