Grouping by Date when the Field has a Time Stamp

[Jen G]

I am doing a very simple query where I need to count by date..the problem is
that the date field is being brought into the table with a time stamp.
Therefore, I cannot group by date, since it is recognizing each record as a
different "date". Example is when I have 3 records on 7/8/2008, but all three
have different time stamps. It is not allowing me to group into one date.

I do not control how the data is coming into the table. Please let me know
how to get around this in a query. I have been dumping the data into excel,
using the mid() formula to get the date only, then doing mid () again because
the date is not coming in rounded, then I am able to group by the date
only....but this is totally tedious.

 

[Douglas J. Steele]

Use the DateValue function.

Instead of

SELECT PurchaseDate, Count(Id)
FROM Table
GROUP BY PurchaseDate

use

SELECT DateValue(PurchaseDate), Count(Id)
FROM Table
GROUP BY DateValue(PurchaseDate)

 

[Wayne-I-M]

Simplest method

Open the query in design view
Right click the date/time field
Open the properties box
Set the format to short date
Group on this

good luck

 

[Douglas J. Steele]

That won't work, Wayne. Setting the format only changes how the data
appears, not what its value is.

You'll end up with multiple rows, all looking like the same date.

 

[Wayne-I-M]

Hi Douglas

I test that answer before I posted it

SELECT TableName.DateTimeField
FROM TableName
GROUP BY TableName.DateTimeField;


Works for me. But you're most likely right.

 

[Douglas J. Steele]

It may be different in different versions of Access.

In the test I did (Access 97), I had three times for the same day, and ended
up with three rows in the result.

 

[Jen G]

Correct. It is changing the view, but still considering it multiple records.
There is the =DatePart Expression that kind of has what I need, but it can
only specify day, month, year...etc...not all three together, unless you know
of anything?

 

[Clif McIrvin]

Correct. It is changing the view, but still considering it multiple
records.
There is the =DatePart Expression that kind of has what I need, but it
can
only specify day, month, year...etc...not all three together, unless
you know
of anything?

Not tested:

try changing Wayne's SQL to:

SELECT TableName.DateTimeField
FROM TableName
GROUP BY INT(TableName.DateTimeField);

(The INT function will strip the hh:mm info)


If that fails it seems that you could create a calculated field in your
query using INT(DateTimeField) and group by that calculated field.

If Access doesn't want to group by a calculated field, try nested
queries: Write a select query that creates the calculated field, then
use that query as the recordsource for the query that is doing the
grouping.

HTH

 

[Douglas J. Steele]

Actually, the proper way is to use the DateValue function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Correct. It is changing the view, but still considering it
multiple records. There is the =DatePart Expression that kind of
has what I need, but it can only specify day, month,
year...etc...not all three together, unless you know of anything?

There are several ways.
expr1: Format([your date field],"yyyymmdd")
expr1: year([your date field]) *10000 + month([your date field])*
100 + day([your date field])

will both return 20080711 for today, one as a string, the other a
number.

Place one of them in the field: row in a new column of the query
design grid and use that to sort and group.