Convert number to month name

[Mark]

Hi,
I am trying to generate a report where one of the fields
is a number that the User enters on a form to represent
the month (1 for January, etc.). The field is setup as a
number in the table and I want the month number converted
to the month name, but when I run the report I
get, "#Error". I've tried using the following line of code
(and some variations) to display the month name on the
report:

=Format(DateSerial(2000,[iMONTH],1),"mmmm")

Does anyone have any suggestions as to what I can do to
convert the month number to the month name. Thanks!
Mark

 

[Rick Brandt]

Hi,
I am trying to generate a report where one of the fields
is a number that the User enters on a form to represent
the month (1 for January, etc.). The field is setup as a
number in the table and I want the month number converted
to the month name, but when I run the report I
get, "#Error". I've tried using the following line of code
(and some variations) to display the month name on the
report:

=Format(DateSerial(2000,[iMONTH],1),"mmmm")

Does anyone have any suggestions as to what I can do to
convert the month number to the month name. Thanks!

Your expression above is fine. Are you sure [IMONTH] contains an integer value
between 1 and 12? Try the expression with a hard-coded integer and see if it works.

 

[Rick Brandt]

Thanks, Rick, I appreciate your help. I still can't get it
to work. I've even tried:

=Choose
([iMONTH],"January","February","March","April","May","June"
,"July","August","September","October","November","December
")

and still get the same error.
Mark

But did you try either expression replacing [iMONTH] with a hard-coded integer value?
If that works, then [iMONTH] is not returning what you think it is. If it doesn't
work with a hard-coded integer, then perhaps you have a reference problem. That
causes many built-in functions in Access to malfunction.

To check that, open a code module and then go to Tools - References. In the
resulting dialog you will see all the libraries registered on your system with the
ones actually used in the current file at the top and "Checked". See if any of those
have the word "Missing" after them.

 

[Rick Brandt]

Yes, I did hard code it and it worked correctly. When I
copy it over to the query builder I get a,"Data type
mismatch in criteria expression". Changing the data type
in the table or re-entering the month numbers makes no
difference as I still get the same error.
Mark

That would suggest that [iMONTH] is not a numerical field. It might be a text field
that just happens to have numerical characters in it, but Access does not always make
an on-the-fly conversion for you.

Try Wrapping CInt() around the [IMONTH] field.

EX:
=Format(DateSerial(2000,CInt([iMONTH]),1),"mmmm")

 

[Mark]

Thanks, Rick, that did the trick. However, now every month
is displayed as December.
Mark

-----Original Message-----

Yes, I did hard code it and it worked correctly. When I
copy it over to the query builder I get a,"Data type
mismatch in criteria expression". Changing the data type
in the table or re-entering the month numbers makes no
difference as I still get the same error.
Mark

That would suggest that [iMONTH] is not a numerical

field. It might be a text field

that just happens to have numerical characters in it, but Access does not always make
an on-the-fly conversion for you.

Try Wrapping CInt() around the [IMONTH] field.

EX:
=Format(DateSerial(2000,CInt([iMONTH]),1),"mmmm")


.

 

[Rick Brandt]

Thanks, Rick, that did the trick. However, now every month
is displayed as December.
Mark

Add another column to your query adjacent to this one with just CInt([iMONTH]) as the
expression. Does that column give you correct output or is it all 12s?

 

[Mark]

I'm not actually using the query builder for the report
because I need to filter out the records where the month
field is empty. When using the query builder I
get, "Invalid use of Null". Therefore, I'm filtering at
the report and setting the criteria as the control source
on the text box. I have also noticed that when I use the
expression, Format(DateSerial(2000,CInt
([iMONTH]),1),"mmmm"), a second time or simply CInt
([iMONTH]) on the report I get, "#Error". Does that help
any?
Mark

-----Original Message-----


Add another column to your query adjacent to this one

with just CInt([iMONTH]) as the

 

[Rick Brandt]

I'm not actually using the query builder for the report
because I need to filter out the records where the month
field is empty. When using the query builder I
get, "Invalid use of Null". Therefore, I'm filtering at
the report and setting the criteria as the control source
on the text box. I have also noticed that when I use the
expression, Format(DateSerial(2000,CInt
([iMONTH]),1),"mmmm"), a second time or simply CInt
([iMONTH]) on the report I get, "#Error". Does that help
any?

Not really. What kind of field is [iMONTH] and what kind of values does it contain?
CInt() will produce #ERROR if the value fed in is Null or not something that can be
converted to an integer. Is there a reason you don't have an actual Date field to
use for this?

 

[Mark]

In the table, iMONTH is defined as follows:
Data Type - Number
Field Size - Long Integer
Decimal places - Auto
Required - No
Indexed - No
Display Control - Text Box

The field is used by a data entry clerk to indicate which
month of the year that a document is handled by her, not
necessarily the month it originated, was made effective,
canceled, etc. It is used to measure document volume and
document dollars that she handles on a monthly basis. The
number is entered as single or double digit into a text
box on a form. I am in the process of converting a Lotus
Approach DB to MS Access 97 and I am rebuilding the old
reports. I deleted the numbers in the iMONTH column of the
new A97 DB then typed directly into the table fields of
the last twelve records a month number for each record (1 -
12) thinking I might have a conversion issue,
but .................
Does this help any?
Mark

-----Original Message-----

I'm not actually using the query builder for the report
because I need to filter out the records where the month
field is empty. When using the query builder I
get, "Invalid use of Null". Therefore, I'm filtering at
the report and setting the criteria as the control source
on the text box. I have also noticed that when I use the
expression, Format(DateSerial(2000,CInt
([iMONTH]),1),"mmmm"), a second time or simply CInt
([iMONTH]) on the report I get, "#Error". Does that help
any?

Not really. What kind of field is [iMONTH] and what kind of values does it contain?
CInt() will produce #ERROR if the value fed in is Null or not something that can be
converted to an integer. Is there a reason you don't have an actual Date field to
use for this?


.

 

[Rick Brandt]

In the table, iMONTH is defined as follows:
Data Type - Number
Field Size - Long Integer
Decimal places - Auto
Required - No
Indexed - No
Display Control - Text Box

The field is used by a data entry clerk to indicate which
month of the year that a document is handled by her, not
necessarily the month it originated, was made effective,
canceled, etc. It is used to measure document volume and
document dollars that she handles on a monthly basis. The
number is entered as single or double digit into a text
box on a form. I am in the process of converting a Lotus
Approach DB to MS Access 97 and I am rebuilding the old
reports. I deleted the numbers in the iMONTH column of the
new A97 DB then typed directly into the table fields of
the last twelve records a month number for each record (1 -
12) thinking I might have a conversion issue,
but .................
Does this help any?

I would assume then that the #ERROR is being caused by rows where [iMONTH] is Null.
Is your query filtering these out? If not, you need an IIf() function to only
perform the calculation when [IMONTH] is not Null.

IIf(IsNull([iMONTH]) = True, "",Format(DateSerial(2000,CInt([iMONTH]),1),"mmmm"))

 

[Mark]

Apparently, iMONTH is not truly null. When I place the IIF
expression in the report it returns all 1707 records.
Before it was returning only 12 records - the last 12 that
I renumbered. Placing the IIF expression in the query
builder returns the error, "Data type mismatch in criteria
expression".
Mark

-----Original Message-----

In the table, iMONTH is defined as follows:
Data Type - Number
Field Size - Long Integer
Decimal places - Auto
Required - No
Indexed - No
Display Control - Text Box

The field is used by a data entry clerk to indicate which
month of the year that a document is handled by her, not
necessarily the month it originated, was made effective,
canceled, etc. It is used to measure document volume and
document dollars that she handles on a monthly basis. The
number is entered as single or double digit into a text
box on a form. I am in the process of converting a Lotus
Approach DB to MS Access 97 and I am rebuilding the old
reports. I deleted the numbers in the iMONTH column of the
new A97 DB then typed directly into the table fields of
the last twelve records a month number for each record (1 -
12) thinking I might have a conversion issue,
but .................
Does this help any?

I would assume then that the #ERROR is being caused by rows where [iMONTH] is Null.
Is your query filtering these out? If not, you need an IIf() function to only
perform the calculation when [IMONTH] is not Null.

IIf(IsNull([iMONTH]) = True, "",Format(DateSerial (2000,CInt([iMONTH]),1),"mmmm"))


.