Group by date/time in a query

[Angel69]

Hi,

I want to count the number of tasks by department by week. I need the time so my date the task was added is formated as a date/time.

I created a query and added the department (twice so that I can group and count), and transaction date. I clicked on totals and added the count function under the department. I added this criteria to the task date: between [start date] and [end date].

Problem is that it's grouping by day and each one is different because all times are different. How do I group these by day and not time?

Thanks for any help!

 

[pbaldy]

Try surrounding the field by the DateValue() function.

 

[Angel69]

I found the function CDate and created: CDate(Int([Updated_Date]))

Although that worked if I want to pull data for 5/29/13 I have to put a start date of 5/28 and end date of 5/30. If I just put from 5/29 to 5/29 I get zero. Any ideas? Thanks!

 

[pbaldy]

Use the same function on the criteria field.

 

[Angel69]

Is this correct:

Between CDate(Int([Start Date])) And CDate(Int([End Date]))

I got a message that the expression is typed incorrectly or is too complex to be evaluated.

 

[pbaldy]

You'd want it on the field, so in SQL view it would look like (and I prefer DateValue):

WHERE DateValue(FieldName) Between [Start Date] And [End Date]

Presumably the criteria are already just dates. It's the data you want to eliminate the times from for comparison.

 

[Angel69]

You're right. DateValue works better and it adds the DateValue to the sql already. I didn't have to add it. Thanks!

 

[pbaldy]

Happy to help!