Datename and datepart

[magmo]

Hi


I have this query that I would like to use DATENAME in, but I'm not sure if this function is supported in Access, I use this often in SQL server. But I can't get it to work in access.

SELECT tbl_WorkHours.WorkDate, Sum(tbl_WorkHours.WorkTime) AS TimeSpent, tbl_Customer.CustomerDescription
FROM tbl_WorkHours INNER JOIN tbl_Customer ON tbl_WorkHours.CustomerID = tbl_Customer.CustomerID
GROUP BY tbl_WorkHours.WorkDate, tbl_Customer.CustomerDescription
HAVING (((tbl_WorkHours.WorkDate) Between #1/1/2005# And #5/25/2005#));

This code give me this result...

WorkDate TimeSpent CustomerDescription
2005-02-28 5 Company 1
2005-03-01 2 Company 1
2005-03-02 0,15 Company 1
2005-03-03 2,35 Company 1
2005-03-03 5 Company 2


but I would like to have like this...

WorkDate TimeSpent CustomerDescription
February 5 Company 1
March 4,50 Company 1
March 5 Company 2



Is this possible, and if so how?


Regards

 

[traingamer]

One way:

Select ...Format([tbl_WorkHours.WorkDate],"mmm") AS WorkMon...
Group by Format([tbl_WorkHours.WorkDate],"mmm")...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[magmo]

Hi

If I change the code to the following...

SELECT tbl_WorkHours.WorkDate, Sum(tbl_WorkHours.WorkTime) AS TimeSpent, tbl_Customer.CustomerDescription
FROM tbl_WorkHours INNER JOIN tbl_Customer ON tbl_WorkHours.CustomerID = tbl_Customer.CustomerID
GROUP BY Format(tbl_WorkHours.WorkDate,"mmm"), tbl_Customer.CustomerDescription
HAVING (((tbl_WorkHours.WorkDate) Between #1/1/2005# And #5/25/2005#));

I get the following error...

"you tryed to execute a query that does not include the specified expression 'WorkDate' as part of an aggregate function"

Anyone knows how to solve this?


Regards

 

[lespaul]

since your select clause contains:

tbl_WorkHours.WorkDate

you have to have that in your group by clause, but you're grouping by

Format(tbl_WorkHours.WorkDate,"mmm")

try this:

SELECT Format(tbl_WorkHours.WorkDate,"mmm"), Sum(tbl_WorkHours.WorkTime) AS TimeSpent, tbl_Customer.CustomerDescription
FROM tbl_WorkHours INNER JOIN tbl_Customer ON tbl_WorkHours.CustomerID = tbl_Customer.CustomerID
WHERE (((tbl_WorkHours.WorkDate) Between #1/1/2005# And #5/25/2005#));
GROUP BY Format(tbl_WorkHours.WorkDate,"mmm"), tbl_Customer.CustomerDescription;

Leslie

 

[traingamer]

That ought to do it (and ought to teach me to cut and paste the whole piece of code rather than just typing the key parts).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill