Showing the serial number of a month in words

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hiya... I'm printing a report from a staff database which shows birthdays by
month. I can't for the life of me find how to print the month name instead
of the serial number (eg 'January' instead of '1') in the report. Can anyone
help?? It's diving me nuts!
 
Hiya... I'm printing a report from a staff database which shows birthdays by
month. I can't for the life of me find how to print the month name instead
of the serial number (eg 'January' instead of '1') in the report. Can anyone
help?? It's diving me nuts!
Click to expand...

No need to bother with the month number.
Just set the format of the [DateOfBirth] control to:
mmmm
 
Thanks FredG and Ken however neither of those suggestions have helped.
Perhaps I should have expanded a little further.

I have the reporting printing under each month's heading the birthdays for
that month with the date of the birthday as well... so what I have currently
is the title Birthdays By Month, then sub-headings for each month (currently
reading '1', '2', '3' instead of 'January', 'February', 'March') and then
under each month there are columns listing the date (eg 14th, 23rd, 25th
etc), each employees name and which office they are based in...

Is that a little clearer?

Appreciate your help very much!
 
What didn't work with the provided suggestions? I would expect them to work
fine. What did you use in the control source of your text box on your
report?
 
OK some more clarity... I was mistaken in advising this was in a report... it
is actually a report running off a query. The query throws out the month in
serial number... what I want the report then to do is to convert the month in
serial number to the month in text (ie. '1' = January)

If I ust set the format of the [DateOfBirth] control to: mmmm as per Fred's
suggestion for some reason will show the first month as December instead of
January and then each subsequent month as January... ie. '1' = December, '2'
= January, '3' = January, '4' = January.

If I use "MonthName([MonthNumberField])" as per Ken's suggestion when I run
the report I am asked to enter a perameter value??

Please keep in mind I am a totally new user with Access so everything needs
to be in idiot-proof language...
 
If your query has a column, how was Ken supposed to know its name? That's
why he substituted [MonthNumberField] as a place holder for your actual
field name which I expect now is DateOfBirth. Did you try:
=MonthName([DateOfBirth])
Keep in mind that if your column contains numbers 1-12 and you want to treat
this column as a "date" value, you will get:
1= Dec 31, 1899
2= Jan 1, 1900
3= Jan 2, 1900
....
12= Jan 11, 1900

--
Duane Hookom
MS Access MVP


Suzy said:
OK some more clarity... I was mistaken in advising this was in a report...
it
is actually a report running off a query. The query throws out the month
in
serial number... what I want the report then to do is to convert the month
in
serial number to the month in text (ie. '1' = January)

If I ust set the format of the [DateOfBirth] control to: mmmm as per
Fred's
suggestion for some reason will show the first month as December instead
of
January and then each subsequent month as January... ie. '1' = December,
'2'
= January, '3' = January, '4' = January.

If I use "MonthName([MonthNumberField])" as per Ken's suggestion when I
run
the report I am asked to enter a perameter value??

Please keep in mind I am a totally new user with Access so everything
needs
to be in idiot-proof language...






Duane Hookom said:
What didn't work with the provided suggestions? I would expect them to
work
fine. What did you use in the control source of your text box on your
report?
Click to expand...
Click to expand...
 
Duane... if I use "MonthName([MonthNumberField])" as per Ken's suggestion
when I run the report I am still asked to enter a perameter value. And yes, I
substituted the field name he supplied with my field name.

I officially give up.


Duane Hookom said:
If your query has a column, how was Ken supposed to know its name? That's
why he substituted [MonthNumberField] as a place holder for your actual
field name which I expect now is DateOfBirth. Did you try:
=MonthName([DateOfBirth])
Keep in mind that if your column contains numbers 1-12 and you want to treat
this column as a "date" value, you will get:
1= Dec 31, 1899
2= Jan 1, 1900
3= Jan 2, 1900
....
12= Jan 11, 1900

--
Duane Hookom
MS Access MVP


Suzy said:
OK some more clarity... I was mistaken in advising this was in a report...
it
is actually a report running off a query. The query throws out the month
in
serial number... what I want the report then to do is to convert the month
in
serial number to the month in text (ie. '1' = January)

If I ust set the format of the [DateOfBirth] control to: mmmm as per
Fred's
suggestion for some reason will show the first month as December instead
of
January and then each subsequent month as January... ie. '1' = December,
'2'
= January, '3' = January, '4' = January.

If I use "MonthName([MonthNumberField])" as per Ken's suggestion when I
run
the report I am asked to enter a perameter value??

Please keep in mind I am a totally new user with Access so everything
needs
to be in idiot-proof language...






Duane Hookom said:
What didn't work with the provided suggestions? I would expect them to
work
fine. What did you use in the control source of your text box on your
report?

--
Duane Hookom
MS Access MVP


Thanks FredG and Ken however neither of those suggestions have helped.
Perhaps I should have expanded a little further.

I have the reporting printing under each month's heading the birthdays
for
that month with the date of the birthday as well... so what I have
currently
is the title Birthdays By Month, then sub-headings for each month
(currently
reading '1', '2', '3' instead of 'January', 'February', 'March') and
then
under each month there are columns listing the date (eg 14th, 23rd,
25th
etc), each employees name and which office they are based in...

Is that a little clearer?

Appreciate your help very much!
Click to expand...
Click to expand...
Click to expand...
 
Try a control source of:
=MonthName([YourActualFieldNameHere])

--
Duane Hookom
MS Access MVP


Suzy said:
Duane... if I use "MonthName([MonthNumberField])" as per Ken's suggestion
when I run the report I am still asked to enter a perameter value. And
yes, I
substituted the field name he supplied with my field name.

I officially give up.


Duane Hookom said:
If your query has a column, how was Ken supposed to know its name? That's
why he substituted [MonthNumberField] as a place holder for your actual
field name which I expect now is DateOfBirth. Did you try:
=MonthName([DateOfBirth])
Keep in mind that if your column contains numbers 1-12 and you want to
treat
this column as a "date" value, you will get:
1= Dec 31, 1899
2= Jan 1, 1900
3= Jan 2, 1900
....
12= Jan 11, 1900

--
Duane Hookom
MS Access MVP


Suzy said:
OK some more clarity... I was mistaken in advising this was in a
report...
it
is actually a report running off a query. The query throws out the
month
in
serial number... what I want the report then to do is to convert the
month
in
serial number to the month in text (ie. '1' = January)

If I ust set the format of the [DateOfBirth] control to: mmmm as per
Fred's
suggestion for some reason will show the first month as December
instead
of
January and then each subsequent month as January... ie. '1' =
December,
'2'
= January, '3' = January, '4' = January.

If I use "MonthName([MonthNumberField])" as per Ken's suggestion when I
run
the report I am asked to enter a perameter value??

Please keep in mind I am a totally new user with Access so everything
needs
to be in idiot-proof language...






:

What didn't work with the provided suggestions? I would expect them to
work
fine. What did you use in the control source of your text box on your
report?

--
Duane Hookom
MS Access MVP


Thanks FredG and Ken however neither of those suggestions have
helped.
Perhaps I should have expanded a little further.

I have the reporting printing under each month's heading the
birthdays
for
that month with the date of the birthday as well... so what I have
currently
is the title Birthdays By Month, then sub-headings for each month
(currently
reading '1', '2', '3' instead of 'January', 'February', 'March') and
then
under each month there are columns listing the date (eg 14th, 23rd,
25th
etc), each employees name and which office they are based in...

Is that a little clearer?

Appreciate your help very much!
Click to expand...
Click to expand...
Click to expand...
 
Suzy,

Try one last thing. Set the source of your control to:

=FORMAT(DateSerial(2000,[YourMonthNumberField],1),"mmm")

John Spencer

Duane said:
Try a control source of:
=MonthName([YourActualFieldNameHere])

--
Duane Hookom
MS Access MVP

Suzy said:
Duane... if I use "MonthName([MonthNumberField])" as per Ken's suggestion
when I run the report I am still asked to enter a perameter value. And
yes, I
substituted the field name he supplied with my field name.

I officially give up.


Duane Hookom said:
If your query has a column, how was Ken supposed to know its name? That's
why he substituted [MonthNumberField] as a place holder for your actual
field name which I expect now is DateOfBirth. Did you try:
=MonthName([DateOfBirth])
Keep in mind that if your column contains numbers 1-12 and you want to
treat
this column as a "date" value, you will get:
1= Dec 31, 1899
2= Jan 1, 1900
3= Jan 2, 1900
....
12= Jan 11, 1900

--
Duane Hookom
MS Access MVP


OK some more clarity... I was mistaken in advising this was in a
report...
it
is actually a report running off a query. The query throws out the
month
in
serial number... what I want the report then to do is to convert the
month
in
serial number to the month in text (ie. '1' = January)

If I ust set the format of the [DateOfBirth] control to: mmmm as per
Fred's
suggestion for some reason will show the first month as December
instead
of
January and then each subsequent month as January... ie. '1' =
December,
'2'
= January, '3' = January, '4' = January.

If I use "MonthName([MonthNumberField])" as per Ken's suggestion when I
run
the report I am asked to enter a perameter value??

Please keep in mind I am a totally new user with Access so everything
needs
to be in idiot-proof language...






:

What didn't work with the provided suggestions? I would expect them to
work
fine. What did you use in the control source of your text box on your
report?

--
Duane Hookom
MS Access MVP


Thanks FredG and Ken however neither of those suggestions have
helped.
Perhaps I should have expanded a little further.

I have the reporting printing under each month's heading the
birthdays
for
that month with the date of the birthday as well... so what I have
currently
is the title Birthdays By Month, then sub-headings for each month
(currently
reading '1', '2', '3' instead of 'January', 'February', 'March') and
then
under each month there are columns listing the date (eg 14th, 23rd,
25th
etc), each employees name and which office they are based in...

Is that a little clearer?

Appreciate your help very much!
Click to expand...
Click to expand...
Click to expand...
 
Duane, my question is similar to this...

I'm in a report, trying to display the month name "October" instead of "10".
I want it to be displayed according to the current month. So, in the
Expression Builder I currently have...

=MonthName(Date())

But I'm still getting "#Error" when I run the report. Any suggestions?
 
The MonthName() function expects a number from 1-12. Date() is the
equivalent of 38630. You can use:

=MonthName(Month(Date()))
Or
=Format(Date(),"mmmm")
 
Got it! Thanks, Duane.

Duane Hookom said:
The MonthName() function expects a number from 1-12. Date() is the
equivalent of 38630. You can use:

=MonthName(Month(Date()))
Or
=Format(Date(),"mmmm")
Click to expand...
 
Back
Top