Grouping records by day

[MikeB]

Hi, me again.

I previously asked a question about importing a very large SQL file
into Excel. I have since managed to obtain the data in XML formal and
the import is now much faster (only about 12 minutes total time vs
about 12 minutes per worksheet with Excel).

One problem remain. In SQL I could truncate the timestamp field of the
records to obtain only a day value. I used this formula =Floor(E2,1)
where E1 was the column with the timestamp value. What I got then was a
date value representing only the day, meaning I could group all records
of a particular day together.

I'm now trying to do the same in Access, but I run up on a problem. The
only way I've found to do this is to construct a new field in the query
as follows: LoanDate: CVTDate(Day([CreationDate]) & "/" &
Month([CreationDate]) & "/" & Year([CreationDate])).

I tried formatting the CreationDate field, but that didn't allow me to
group the records together, each record grouped on its own.

Is there a smarter way to extract the Date portion of the timestamp and
grouping on it?

Thanks

 

[John Vinson]

Is there a smarter way to extract the Date portion of the timestamp and
grouping on it?

Yep. Just use Fix instead of Floor.

John W. Vinson[MVP]

 

[Van T. Dinh]

DateValue([YouDateTimeField]) should give you the date-only value.

 

[MikeB]

Thanks John, this works a treat.

Mike

Yep. Just use Fix instead of Floor.

John W. Vinson[MVP]

 

[MikeB]

I tried this, but when I group on the value, I still got different
groupings for each record based on the timestamp differences between
the records.

Thanks for the help anyway.

Mike

DateValue([YouDateTimeField]) should give you the date-only value.

--
HTH
Van T. Dinh
MVP (Access)

Hi, me again.

I previously asked a question about importing a very large SQL file
into Excel. I have since managed to obtain the data in XML formal and
the import is now much faster (only about 12 minutes total time vs
about 12 minutes per worksheet with Excel).

One problem remain. In SQL I could truncate the timestamp field of the
records to obtain only a day value. I used this formula =Floor(E2,1)
where E1 was the column with the timestamp value. What I got then was a
date value representing only the day, meaning I could group all records
of a particular day together.

I'm now trying to do the same in Access, but I run up on a problem. The
only way I've found to do this is to construct a new field in the query
as follows: LoanDate: CVTDate(Day([CreationDate]) & "/" &
Month([CreationDate]) & "/" & Year([CreationDate])).

I tried formatting the CreationDate field, but that didn't allow me to
group the records together, each record grouped on its own.

Is there a smarter way to extract the Date portion of the timestamp and
grouping on it?

Thanks