I have a table with a date field. I need to group it by month.
I was thinking I could turn the number (37178) into a string (14-Oct-01) and
then trim the left3 and right 3 to just leave Oct.
Then I could group it by month.

Is there a better way of doing this?

what would the SQL statement look like?

thanks

Create two calculated fields in your query:
ThisYear: Year([MyDate])
ThisMonth: Month([MyDate])
Group by those fields.

If this query is the source for a report, the Sorting And Grouping dialog is
capable of grouping by month.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Santiago Gomez" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a table with a date field. I need to group it by month.
> I was thinking I could turn the number (37178) into a string (14-Oct-01)
and
> then trim the left3 and right 3 to just leave Oct.
> Then I could group it by month.
>
> Is there a better way of doing this?
>
> what would the SQL statement look like?
>
> thanks
>
>

Easier than you think:

?Format(37178, "mmm")
Oct

Use Allen's Month & Year for Sorting and Grouping in Reports.

--
HTH
Van T. Dinh
MVP (Access)



"Santiago Gomez" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a table with a date field. I need to group it by month.
> I was thinking I could turn the number (37178) into a string (14-Oct-01)
and
> then trim the left3 and right 3 to just leave Oct.
> Then I could group it by month.
>
> Is there a better way of doing this?
>
> what would the SQL statement look like?
>
> thanks
>
>

Thanks that worked.

"Van T. Dinh" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Easier than you think:
>
> ?Format(37178, "mmm")
> Oct
>
> Use Allen's Month & Year for Sorting and Grouping in Reports.
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Santiago Gomez" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I have a table with a date field. I need to group it by month.
> > I was thinking I could turn the number (37178) into a string (14-Oct-01)
> and
> > then trim the left3 and right 3 to just leave Oct.
> > Then I could group it by month.
> >
> > Is there a better way of doing this?
> >
> > what would the SQL statement look like?
> >
> > thanks
> >
> >
>
>