Pull Month from Full Date

[Beverly-Texas]

Hello,

I have a date column showing up in a report and I want to sort by Month
only. So I want all of the January lines grouped together, regardless of day
or year. But I can't figure out how to pull just the month from that field.

Do I change something in the Control Source area for that field under the
"Detail" section of the design mode of the report? I tried doing Month() but
that didn't work - I probably wasn't using it correctly.

Any help is appreciated. Thanks in advance!
Beverly

 

[Bryan in Bakersfield]

Beverly -

I'm not sure what the problem was, but setting the Control Source of the
Text Box to
=Month([YourDateField])

should have worked.

You can also use
=Format([Call Date],"mm")
or
=Format([Call Date],"mmm") to get the month name.

Do you get an error or #Name in the feld?


Bryan

 

[Wayne-I-M]

Hi Beverly

You can simply get the month number like this

DatePart("M",[YourTable]![Datefield])

But for a report you don't need to. Open the report in design view and use
the sorting and grouping box. Select the date field and (at the bottom of
the box) select group by Month

You can add a group footer and header and put your monthly totals into that

 

[Beverly-Texas]

Thanks Wayne.

But when I group like that (via sorting and grouping), it doesn't group by
month - not excluding the year. It is separating the months by months AND
year. I want all January together, all February together, etc.

Hi Beverly

You can simply get the month number like this

DatePart("M",[YourTable]![Datefield])

But for a report you don't need to. Open the report in design view and use
the sorting and grouping box. Select the date field and (at the bottom of
the box) select group by Month

You can add a group footer and header and put your monthly totals into that


--
Wayne
Manchester, England.

Hello,

I have a date column showing up in a report and I want to sort by Month
only. So I want all of the January lines grouped together, regardless of day
or year. But I can't figure out how to pull just the month from that field.

Do I change something in the Control Source area for that field under the
"Detail" section of the design mode of the report? I tried doing Month() but
that didn't work - I probably wasn't using it correctly.

Any help is appreciated. Thanks in advance!
Beverly

 

[Beverly-Texas]

Hi Bryan,

Thanks. Yes, the first one gives me #Error. The 2nd and third one prompt
me for "Call Date" - which I don't kwow what to put there. I want the report
to pull the full 12 months. If I just hit "OK" it pulls the report, but no
months are shown and it doesn't group by month either.

Do you know why I'm getting the #Error?

Thanks again.

Beverly -

I'm not sure what the problem was, but setting the Control Source of the
Text Box to
=Month([YourDateField])

should have worked.

You can also use
=Format([Call Date],"mm")
or
=Format([Call Date],"mmm") to get the month name.

Do you get an error or #Name in the feld?


Bryan

Hello,

I have a date column showing up in a report and I want to sort by Month
only. So I want all of the January lines grouped together, regardless of day
or year. But I can't figure out how to pull just the month from that field.

Do I change something in the Control Source area for that field under the
"Detail" section of the design mode of the report? I tried doing Month() but
that didn't work - I probably wasn't using it correctly.

Any help is appreciated. Thanks in advance!
Beverly

 

[Wayne-I-M]

If you want to group all Januarys and Februarys etc
use
DatePart("M",[YourTable]![Datefield])
in the query the report is based on.
Bring this into the report and group on that

This will give you
Jan 2006 and Jan 2007 and Jan 2008 etc all in one group.

But you can improve on this by adding another group (by year) to the
grouping so yu can tally all the months (say all Jans and Febs etc) plus you
can also tally just the months from each year. Or you could add
DatePart("yyyy",[YourTable]![Datefield]) and group on that as well as the
month.

You just need to play around with it and see what you like best





--
Wayne
Manchester, England.

Thanks Wayne.

But when I group like that (via sorting and grouping), it doesn't group by
month - not excluding the year. It is separating the months by months AND
year. I want all January together, all February together, etc.

Hi Beverly

You can simply get the month number like this

DatePart("M",[YourTable]![Datefield])

But for a report you don't need to. Open the report in design view and use
the sorting and grouping box. Select the date field and (at the bottom of
the box) select group by Month

You can add a group footer and header and put your monthly totals into that


--
Wayne
Manchester, England.

Hello,

I have a date column showing up in a report and I want to sort by Month
only. So I want all of the January lines grouped together, regardless of day
or year. But I can't figure out how to pull just the month from that field.

Do I change something in the Control Source area for that field under the
"Detail" section of the design mode of the report? I tried doing Month() but
that didn't work - I probably wasn't using it correctly.

Any help is appreciated. Thanks in advance!
Beverly

 

[Beverly-Texas]

Thanks again.
So I tried the first solution you proposed but I am not that proficient in
Access so I may need a little more help. I opened the query, went to a new
column, and added this:
=DatePart("M",[Project Info]![Month Sold])
in the Criteria field. I also checked the "Show" field and in the Field
drop-down, I just typed in "Month" which turned into "Expr1: [Month]".

Project Info is the name of the table that the Month Sold field is in so
hopefully I did that right.

When I click on "View" to see results, I get a prompt for "Month." How do I
pull all months?

Thanks,
Beverly

If you want to group all Januarys and Februarys etc
use
DatePart("M",[YourTable]![Datefield])
in the query the report is based on.
Bring this into the report and group on that

This will give you
Jan 2006 and Jan 2007 and Jan 2008 etc all in one group.

But you can improve on this by adding another group (by year) to the
grouping so yu can tally all the months (say all Jans and Febs etc) plus you
can also tally just the months from each year. Or you could add
DatePart("yyyy",[YourTable]![Datefield]) and group on that as well as the
month.

You just need to play around with it and see what you like best





--
Wayne
Manchester, England.

Thanks Wayne.

But when I group like that (via sorting and grouping), it doesn't group by
month - not excluding the year. It is separating the months by months AND
year. I want all January together, all February together, etc.

Hi Beverly

You can simply get the month number like this

DatePart("M",[YourTable]![Datefield])

But for a report you don't need to. Open the report in design view and use
the sorting and grouping box. Select the date field and (at the bottom of
the box) select group by Month

You can add a group footer and header and put your monthly totals into that


--
Wayne
Manchester, England.



:

Hello,

I have a date column showing up in a report and I want to sort by Month
only. So I want all of the January lines grouped together, regardless of day
or year. But I can't figure out how to pull just the month from that field.

Do I change something in the Control Source area for that field under the
"Detail" section of the design mode of the report? I tried doing Month() but
that didn't work - I probably wasn't using it correctly.

Any help is appreciated. Thanks in advance!
Beverly

 

[Wayne-I-M]

In the query

Go to a blank column
Put this
MonthNumberatePart("M",[Project Info]![Month Sold])
Save the query

Open the report
Select View
Select Fields
Drag the MonthNumber into the report (into the detail line)
Select Sorting and Grouping
Select MonthNumber from the drop down

In the box at the bottom of the sorting and grouping box
Select Group Header = Yes
Close the box
Drag the MonthNumber into the new header area.

View the report
--
Wayne
Manchester, England.

Thanks again.
So I tried the first solution you proposed but I am not that proficient in
Access so I may need a little more help. I opened the query, went to a new
column, and added this:
=DatePart("M",[Project Info]![Month Sold])
in the Criteria field. I also checked the "Show" field and in the Field
drop-down, I just typed in "Month" which turned into "Expr1: [Month]".

Project Info is the name of the table that the Month Sold field is in so
hopefully I did that right.

When I click on "View" to see results, I get a prompt for "Month." How do I
pull all months?

Thanks,
Beverly

If you want to group all Januarys and Februarys etc
use
DatePart("M",[YourTable]![Datefield])
in the query the report is based on.
Bring this into the report and group on that

This will give you
Jan 2006 and Jan 2007 and Jan 2008 etc all in one group.

But you can improve on this by adding another group (by year) to the
grouping so yu can tally all the months (say all Jans and Febs etc) plus you
can also tally just the months from each year. Or you could add
DatePart("yyyy",[YourTable]![Datefield]) and group on that as well as the
month.

You just need to play around with it and see what you like best





--
Wayne
Manchester, England.

Thanks Wayne.

But when I group like that (via sorting and grouping), it doesn't group by
month - not excluding the year. It is separating the months by months AND
year. I want all January together, all February together, etc.

:

Hi Beverly

You can simply get the month number like this

DatePart("M",[YourTable]![Datefield])

But for a report you don't need to. Open the report in design view and use
the sorting and grouping box. Select the date field and (at the bottom of
the box) select group by Month

You can add a group footer and header and put your monthly totals into that


--
Wayne
Manchester, England.



:

Hello,

I have a date column showing up in a report and I want to sort by Month
only. So I want all of the January lines grouped together, regardless of day
or year. But I can't figure out how to pull just the month from that field.

Do I change something in the Control Source area for that field under the
"Detail" section of the design mode of the report? I tried doing Month() but
that didn't work - I probably wasn't using it correctly.

Any help is appreciated. Thanks in advance!
Beverly

 

[Beverly-Texas]

Thank you!!!!!! That worked!!!!!

In the query

Go to a blank column
Put this
MonthNumberatePart("M",[Project Info]![Month Sold])
Save the query

Open the report
Select View
Select Fields
Drag the MonthNumber into the report (into the detail line)
Select Sorting and Grouping
Select MonthNumber from the drop down

In the box at the bottom of the sorting and grouping box
Select Group Header = Yes
Close the box
Drag the MonthNumber into the new header area.

View the report
--
Wayne
Manchester, England.

Thanks again.
So I tried the first solution you proposed but I am not that proficient in
Access so I may need a little more help. I opened the query, went to a new
column, and added this:
=DatePart("M",[Project Info]![Month Sold])
in the Criteria field. I also checked the "Show" field and in the Field
drop-down, I just typed in "Month" which turned into "Expr1: [Month]".

Project Info is the name of the table that the Month Sold field is in so
hopefully I did that right.

When I click on "View" to see results, I get a prompt for "Month." How do I
pull all months?

Thanks,
Beverly

If you want to group all Januarys and Februarys etc
use
DatePart("M",[YourTable]![Datefield])
in the query the report is based on.
Bring this into the report and group on that

This will give you
Jan 2006 and Jan 2007 and Jan 2008 etc all in one group.

But you can improve on this by adding another group (by year) to the
grouping so yu can tally all the months (say all Jans and Febs etc) plus you
can also tally just the months from each year. Or you could add
DatePart("yyyy",[YourTable]![Datefield]) and group on that as well as the
month.

You just need to play around with it and see what you like best





--
Wayne
Manchester, England.



:

Thanks Wayne.

But when I group like that (via sorting and grouping), it doesn't group by
month - not excluding the year. It is separating the months by months AND
year. I want all January together, all February together, etc.

:

Hi Beverly

You can simply get the month number like this

DatePart("M",[YourTable]![Datefield])

But for a report you don't need to. Open the report in design view and use
the sorting and grouping box. Select the date field and (at the bottom of
the box) select group by Month

You can add a group footer and header and put your monthly totals into that


--
Wayne
Manchester, England.



:

Hello,

I have a date column showing up in a report and I want to sort by Month
only. So I want all of the January lines grouped together, regardless of day
or year. But I can't figure out how to pull just the month from that field.

Do I change something in the Control Source area for that field under the
"Detail" section of the design mode of the report? I tried doing Month() but
that didn't work - I probably wasn't using it correctly.

Any help is appreciated. Thanks in advance!
Beverly

 

[Wayne-I-M]

One last thing - to make it better for a user to understand you could change
the query calculation to
MonthNumber: Format([YourTable]![Datefield],"mmmm")


Good luck with your project

--
Wayne
Manchester, England.

Thank you!!!!!! That worked!!!!!

In the query

Go to a blank column
Put this
MonthNumberatePart("M",[Project Info]![Month Sold])
Save the query

Open the report
Select View
Select Fields
Drag the MonthNumber into the report (into the detail line)
Select Sorting and Grouping
Select MonthNumber from the drop down

In the box at the bottom of the sorting and grouping box
Select Group Header = Yes
Close the box
Drag the MonthNumber into the new header area.

View the report
--
Wayne
Manchester, England.

Thanks again.
So I tried the first solution you proposed but I am not that proficient in
Access so I may need a little more help. I opened the query, went to a new
column, and added this:
=DatePart("M",[Project Info]![Month Sold])
in the Criteria field. I also checked the "Show" field and in the Field
drop-down, I just typed in "Month" which turned into "Expr1: [Month]".

Project Info is the name of the table that the Month Sold field is in so
hopefully I did that right.

When I click on "View" to see results, I get a prompt for "Month." How do I
pull all months?

Thanks,
Beverly

:

If you want to group all Januarys and Februarys etc
use
DatePart("M",[YourTable]![Datefield])
in the query the report is based on.
Bring this into the report and group on that

This will give you
Jan 2006 and Jan 2007 and Jan 2008 etc all in one group.

But you can improve on this by adding another group (by year) to the
grouping so yu can tally all the months (say all Jans and Febs etc) plus you
can also tally just the months from each year. Or you could add
DatePart("yyyy",[YourTable]![Datefield]) and group on that as well as the
month.

You just need to play around with it and see what you like best





--
Wayne
Manchester, England.



:

Thanks Wayne.

But when I group like that (via sorting and grouping), it doesn't group by
month - not excluding the year. It is separating the months by months AND
year. I want all January together, all February together, etc.

:

Hi Beverly

You can simply get the month number like this

DatePart("M",[YourTable]![Datefield])

But for a report you don't need to. Open the report in design view and use
the sorting and grouping box. Select the date field and (at the bottom of
the box) select group by Month

You can add a group footer and header and put your monthly totals into that


--
Wayne
Manchester, England.



:

Hello,

I have a date column showing up in a report and I want to sort by Month
only. So I want all of the January lines grouped together, regardless of day
or year. But I can't figure out how to pull just the month from that field.

Do I change something in the Control Source area for that field under the
"Detail" section of the design mode of the report? I tried doing Month() but
that didn't work - I probably wasn't using it correctly.

Any help is appreciated. Thanks in advance!
Beverly

 

[Bryan in Bakersfield]

Bev -

Apparently, the Month() function wasn't recognizing your date field as date.

For the other two examples, I had used my field name - I forgot to change
those to [YourDateField] when I replied.

Glad to hear that you have it working.


Bryan

Hi Bryan,

Thanks. Yes, the first one gives me #Error. The 2nd and third one prompt
me for "Call Date" - which I don't kwow what to put there. I want the report
to pull the full 12 months. If I just hit "OK" it pulls the report, but no
months are shown and it doesn't group by month either.

Do you know why I'm getting the #Error?

Thanks again.

Beverly -

I'm not sure what the problem was, but setting the Control Source of the
Text Box to
=Month([YourDateField])

should have worked.

You can also use
=Format([Call Date],"mm")
or
=Format([Call Date],"mmm") to get the month name.

Do you get an error or #Name in the feld?


Bryan

Hello,

I have a date column showing up in a report and I want to sort by Month
only. So I want all of the January lines grouped together, regardless of day
or year. But I can't figure out how to pull just the month from that field.

Do I change something in the Control Source area for that field under the
"Detail" section of the design mode of the report? I tried doing Month() but
that didn't work - I probably wasn't using it correctly.

Any help is appreciated. Thanks in advance!
Beverly