TheCandyman
Technical User
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
The SQL i'm trying to use is:
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?
Code:
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:
Code:
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?