SELECT TheMonth
FROM
(SELECT DISTINCT
Format([TheDay],"mmmm") AS TheMonth
, Format([TheDay],"mm") AS TheOrder
FROM CalendarTable) as T
ORDER BY TheOrder
Or do it with two queries.
SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
, Format(tblCalendar.Day,"mm") as TheOrder
FROM tblCalendar
Save that as qMonths and build another query on it.
SELECT qMonths.Month
FROM qMonths
ORDER BY theOrder
Of course you could always use
SELECT DISTINCT
Format([TheDay],"mmmm") AS TheMonth
, Format([TheDay],"mm") AS TheOrder
FROM CalendarTable
ORDER BY Format([TheDay],"mm")
And not display the TheOrder field on your report or in the form. If it is
the source for a listbox or a combobox, just HIDE the TheOrder column by
setting its column width to zero.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Chad said:
Karl,
Thanks again for your quick response. Unfortunately this also returns
duplicate values if I have dates associated with the same month in any
other
year. Sorry if I am being a pain. Let me know if you can think of
anything
else.
Thanks,
Chad
KARL DEWEY said:
Try this ---
SELECT Format([tblCalendar].[Day],"mm"),
Format([tblCalendar].[Day],"mmmm") AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"mm");
--
KARL DEWEY
Build a little - Test a little
Chad said:
Karl,
Thanks for the quick reply, unfortunately that returns unique values
for
for month and year.
For Example:
If I have data spanning more than one yearthen it will return the
month
more than once.
Is there a way to only return each Distinct Month?
Thanks,
Chad
:
Try this ---
SELECT Format([tblCalendar].[Day],"yyyymm"),
Format([tblCalendar].[Day],"mmmm") AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"yyyymm");
--
KARL DEWEY
Build a little - Test a little
:
I have a table that has a bunch of dates for about and I would like
to write
a query that returns each DISTINCT month found in that table:
Here is the query that I have now, which sorts them alphabetically:
SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"mmmm");
If I try to sort it on the actual data on in the field, like:
SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
FROM tblCalendar
ORDER BY tblCalendar].[Day;
It returns an error saying it conflicts with my DISTINCT statement.
Does
anyone know of any ways around this?
Thanks,
Chad
