Group by Date, not DateTime 1

[TheCandyman]

I'm trying to get a query to group records by the day and not by each time on that date. Here is some data i'm working with

RegDate	                SumOfAmountPaid
8/2/2010 10:12:59 AM	$50.35
8/3/2010 1:19:54 PM	$302.00
8/3/2010 2:32:24 PM	$25.00
8/5/2010 9:37:04 AM	$31.65
8/5/2010 3:07:45 PM	$36.20
8/9/2010 9:44:41 AM	$35.15
8/12/2010 9:37:05 PM	$50.00
8/16/2010 9:40:46 AM	$26.05
8/16/2010 9:43:17 AM	$31.30

The SQL i'm trying to use is:

SELECT tbl_Clas_Ads.RegDate, Sum(tbl_Payment.AmountPaid) AS SumOfAmountPaid
FROM tbl_Clas_Ads INNER JOIN tbl_Payment ON tbl_Clas_Ads.Ad_ID = tbl_Payment.Ad_ID
WHERE (tbl_Payment.TransID Is Not Null)
GROUP BY tbl_Clas_Ads.[RegDate]
HAVING ((tbl_Clas_Ads.[RegDate]>=#8/1/2010#) And (tbl_Clas_Ads.[RegDate]<#9/1/2010#));

I'm hitting this from a web page (ASP), so I have tried a few suggestions i found while Googleing, but couldn't find anything that would work. I know there is the Format(XXX,"short date") which only works within MS Access but not from a page running ASP. Any ideas how to get this to work?

 

[MajP]

GROUP BY Int(tbl_Clas_Ads.[RegDate])

 

[TheCandyman]

I tried your suggestion:

SELECT tbl_Clas_Ads.RegDate, Sum(tbl_Payment.AmountPaid) AS SumOfAmountPaid FROM tbl_Clas_Ads INNER JOIN tbl_Payment ON tbl_Clas_Ads.Ad_ID = tbl_Payment.Ad_ID WHERE (tbl_Payment.TransID Is Not Null) GROUP BY Int(tbl_Clas_Ads.RegDate) HAVING ((tbl_Clas_Ads.[RegDate]>=#8/1/2010#) And (tbl_Clas_Ads.[RegDate]<#9/1/2010#));

But i get this error:
You tried to execute a query that does not include the specified expression 'RegDate' as part of an aggregate function.


As soon as i remove the 'Int' from the SQL it works again. The Int is an ASP command, not a SQL one i believe. Any other suggestions?

 

[PHV]

SELECT Int(A.RegDate) AS RegDay, Sum(P.AmountPaid) AS SumOfAmountPaid
FROM tbl_Clas_Ads A INNER JOIN tbl_Payment P ON A.Ad_ID = P.Ad_ID
WHERE P.TransID Is Not Null AND Int(A.RegDate) Between #2010-08-01# And #2010-08-31#
GROUP BY Int(A.RegDate)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheCandyman]

That worked! Plus, i learned about the 'Int' command too.