I need to run the query below:

ELECT WGMAHIST_TIMEDTY.TDYPT, MID(WGMAHIST_TIMEDTY.TDYTDT,1,4) AS Year, SUM(WGMAHIST_TIMEDTY.TDYFND) AS TotHrs
FROM WGMAHIST_TIMEDTY
Where WGMAHIST_TIMEDTY.TDYTDT Between 19981001 AND 20090930
Group By WGMAHIST_TIMEDTY.TDYPT, Year

But I am having a problem with the term Year in the group by clause. How do I group by just the year portion of the date field which is yyyymmdd?

Thanks.

Try grouping by the expression rather than the alias:

MID(WGMAHIST_TIMEDTY.TDYTDT,1,4)

1. do not use YEAR as your column name as it is an Access Reserved Word.

2. You would use:

ELECT WGMAHIST_TIMEDTY.TDYPT, MID(WGMAHIST_TIMEDTY.TDYTDT,1,4) AS MyYear, SUM(WGMAHIST_TIMEDTY.TDYFND) AS TotHrs
FROM WGMAHIST_TIMEDTY
Where WGMAHIST_TIMEDTY.TDYTDT Between 19981001 AND 20090930
Group By WGMAHIST_TIMEDTY.TDYPT, MID(WGMAHIST_TIMEDTY.TDYTDT,1,4)

Try grouping by the expression rather than the alias:

MID(WGMAHIST_TIMEDTY.TDYTDT,1,4)

too quick for me. :D :p :D

1. do not use YEAR as your column name as it is an Access Reserved Word.

2. You would use:

ELECT WGMAHIST_TIMEDTY.TDYPT, MID(WGMAHIST_TIMEDTY.TDYTDT,1,4) AS MyYear, SUM(WGMAHIST_TIMEDTY.TDYFND) AS TotHrs
FROM WGMAHIST_TIMEDTY
Where WGMAHIST_TIMEDTY.TDYTDT Between 19981001 AND 20090930
Group By WGMAHIST_TIMEDTY.TDYPT, MID(WGMAHIST_TIMEDTY.TDYTDT,1,4)

I have tried putting the expression into the group by but I get an ODBC error.

ODBC--call failed.
Argument 1 of function SUBSTRING not valid. (#-171)

Any suggestions?

Does the query run without the group by?

Does the query run without the group by?

Yes, it runs.

Try using the query without the grouping and then add that into a new query and do the grouping there.