Problem with GROUP BY [Date Time]

[JapanFreak]

Hi there,

I have a table called tblTransactions with columns called (among others) [Date Time], Ticker, Quantity, Price and Commission. Column [Date Time] contains both a date and a time.
I want to determine the commission paid per day. If I use the following code I still get all single trades on a day as the times at which the trades were exucuted are all different. Is it possible to group by full days only without having to change my database in order to determine the daily commission paid?

SELECT T.[Date Time], sum(Commission) AS TotalCommission
tblTransactions AS T
WHERE T.[Date Time]<=#04/17/07#
GROUP BY T.[Date Time];

Best regards,
JapanFreak

 

[EMP]

You can use the DateValue() function to extract the dates.

SELECT DateValue(T.[Date Time]), sum(Commission) AS TotalCommission
tblTransactions AS T
WHERE T.[Date Time]<=#04/17/07#
GROUP BY DateValue(T.[Date Time]);

^

 

[JapanFreak]

Does not work...

Hi there,

thank you very much for your help. Unfortunately, this code still does not work. I get an error message, which says (I have to translate a German error message) that the criteria types in the criteria expression are incompatible (I hope you know what I mean)... What could be the reason for that?

SELECT datevalue(T.[Date Time]), sum(Commission) AS TotalCommission
FROM tblTransactionsUSD AS T
WHERE T.[Date Time] <= #04/17/07#
GROUP BY datevalue(T.[Date Time]);

Thank you very much for your help.

Best regards,
JapanFreak

 

[WayneRyan]

JF,

Your query is definitely sound.

Are you sure that your [Date Time] field is really DateTime?

I'm pretty sure that [Date Time] is a text field ... and it
has some values in it that really aren't dates.

Or [Commission] is text and has some non-numeric values.

hth,
Wayne

 

[JapanFreak]

Problem solved

Thank you very much for your posts. Yes, I apology, I was such a stupid mistake. After formatting correctly it eventually worked.

Best regards
JapanFreak