Group records by month in a "totals" query (MS Access)

[cdm777]

In a MS Access 2003 database with Powerbuilder 7.0, I am trying to run a query on a table 'expense' that has a date/time column called 'month'.

I want it to return all the months that are contained in the table: e.g.,
200507
200508
200509
200510
etc.

I have run the following queries (and various other combinations) with no success:

SELECT YrMnth: format( expense.month , "yyyymm" )
FROM expense
GROUP BY YrMnth: format( expense.month , "yyyymm" );

---removing 'YrMnth: '---
SELECT format( expense.month , "yyyymm" )
FROM expense
GROUP BY format( expense.month , "yyyymm" );

THANKS!!

 

[Trevil]

Since you say it's a date/time field:

SELECT Year([Month]) & Right(Month([Month])+100,2) AS YrMnth
FROM expense
GROUP BY Year([Month]) & Right(Month([Month])+100,2);

"Hmmm, it worked when I tested it....

 

[PHV]

SELECT DISTINCT Format([month], 'yyyymm') AS YrMnth FROM expense;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886