How to change Number to Month

[davidg47]

I have a report that returns the month from a table where the month is entered as a number. Is there a way to change the number returned into a word? For example, when the 1 is returned, can I somehow change it to January, or when the number 6 is returned, change it to June... and so forth? So that in the report, instead of the supervisor seeing the number 3 for the month of March, the supervisor would actually see "March".

Thanks in advance for your help...

D.

 

[mhartman]

Hello:
The below sub would do the trick. I takes a digit assigned to txtMonth and converts this value to the appropriate month and places it in txtMonth2
'
Public Sub GetMonth()
SelectCase txtMonth
Case 1
txtMonth2 = "January"
Case 2
txtMonth2 = "February"
Case 3
txtMonth2 = "March"
Case 4
txtMonth2 = "April"
Case 5
txtMonth2 = "May"
Case 6
txtMonth2 = "June"
Case 7
txtMonth2 = "July"
Case 8
txtMonth2 = "August"
Case 9
txtMonth2 = "September"
Case 10
txtMonth2 = "October"
Case 11
txtMonth2 = "November"
Case 12
txtMonth2 = "December"
End Sub
'
Regards
Mark

 

[selenau837]

If I'm thinking correctly you can just the format function to get the results you want.

in that field put Format([fieldname], "mmmm")

that should return the full name for that month.

 

[davidg47]

Forgive my stupidity, but maybe if I explain this to you in a little more detail, then maybe you can give me a little more detail in how to make this work.

I have a query named BenefitChargeDetail that this report uses as it's datasource.

The name of the field that is returning the number of the month is the StatementPeriodMonth. It is a numerical field, not a text field.

In the first suggestion, are you saying I should create a second field named txtMonth2? And where should the code you provided be put?

In the second suggestion, should I put that expression directly in the text box in the design of the report? I did try that, and tried it in the properties of the field, and got errors... but, that doesn't mean I did it right.

Thanks again for all your help...
D.

 

[mhartman]

Hello:
Thanks for the extra information. If you have a query set up, then in design view, insert a new column next to your StatementPeriodMonth field. In the field heading for that column place Format(StatementPeriodMonth,"mmmm") as Selenau837 states. Then run your query. That should do it. base you report on this query.
Regards
Mark

 

[mhartman]

Format(StatementPeriodMonth,"mmmm")

 

[davidg47]

WOO HOO!!! it worked!

But, something weird happened. It is returning December for the number 1, and January for the number 2, and then January again for the number 3.

Any idea of what's going on with that?

Thanks so much for your help...

Dave

 

[selenau837]

WOO HOO!!! it worked!

But, something weird happened. It is returning December for the number 1, and January for the number 2, and then January again for the number 3.

Any idea of what's going on with that?

Thanks so much for your help...

Dave

Can you please paste a copy of the SQL for your query?

 

[selenau837]

Also, another option is to remove the formatting from that field.

Leave it as StatementPeriodMonth to make sure what numbers it is pulling.

Because the formatting should work correctly.

 

[davidg47]

Here is the SQL view:

SELECT BenefitCharges.SSN, BenefitCharges.ChargesThisStatementPeriod, BenefitCharges.CreditsThisStatementPeriod, BenefitCharges.CreditsDue, MasterLIJ.SSN, MasterLIJ.LastName, MasterLIJ.[Reason Desc], MasterLIJ.Code, MasterLIJ.ICDateReceived, MasterLIJ.ICDateReplied, MasterLIJ.FinalStatus, MasterLIJ.[Pot_Chg_$], MasterLIJ.LocationCode, [Status Table].StatusDescr, BenefitCharges.StatementPeriodDay, BenefitCharges.StatementPeriodWeek, BenefitCharges.StatementPeriodMonth, Format([StatementPeriodMonth],"mmmm") AS Expr1, BenefitCharges.StatementPeriodQuarter, BenefitCharges.StatementPeriodYear, MasterLIJ.ClaimStatus
FROM (BenefitCharges INNER JOIN MasterLIJ ON BenefitCharges.SSN = MasterLIJ.SSN) INNER JOIN [Status Table] ON MasterLIJ.ClaimStatus = [Status Table].ClaimStatusID;


Thanks again for all your help...

Dave

 

[selenau837]

Have you tried to add the table name infront of the field name. ? See below:


Format(BenefitCharges.StatementPeriodMonth,"mmmm") AS Expr1

 

[davidg47]

Nope, sorry, still the same result. Could it be that the month (entered as a number) is entered as only one digit instead of 2 digits? Maybe the report or database is looking for 2 digits as in mm/dd/yyyy.

 

[selenau837]

Nope, sorry, still the same result. Could it be that the month (entered as a number) is entered as only one digit instead of 2 digits? Maybe the report or database is looking for 2 digits as in mm/dd/yyyy.

Can you make it see 01 instead of 1? I really couldn't tell you without looking at your DB. Can you zip it and upload it for me to look at?

 

[raskew]

Your month number is being interpreted as a date, as stored internally by Access. You can see this from the debug (immediate) window with this:

? format(1, "short date")
12/31/99  'that's 1899.  So when you attempt to return the month, you get "Dec"

? format(2, "short date) returns
1/1/00  'that's 1900.  Thus, all numbers 2 - 12 will return "Jan" when formatted as a month

A workaround could be (example, you submit 11 expecting to return "Nov":

? format(dateserial(year(date), 11,1), "mmm")
Nov

Added:

 An additional option might be the Choose() function.  Example:
x = 12
? choose(x, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
December

Added: This MSKB link describes how dates are stored in MS Access: http://support.microsoft.com/kb/q130514/

HTH - Bob

 

[raskew]

I've never before 'bumped' a post but, in this case, think it's an important and mysterious issue. Attempting to sort by month, using the month number, kind of defies ordinary logic. Hope this (see previous post) can provide some insight.
Bob

 

[davidg47]

What does "bump a post" mean?

 

[raskew]

"Bumping a post" = Providing a (probably not helpful) response to the thread in order to push the thread back to the top of the viewing list. For example, your question took a thread that was rapidly falling off the radar screen and pushed it back (based on date/time of most recent response) to the top of the list.

Added: And, when I responded, a few hours later, the thread once again popped up to the top of the list (date/time of the most recent response). It was 'bumped' without providing any valuable information.

HTH - Bob

 

[raskew]

I've lied to you (not intentionally). Editing an existing post, as I did in the previous post, won't result in a 'bump'. This, however (a brand-new response) will!

Best wishes - Bob

 

[tjcinnamon]

Is there a resolution to this problem?

 

[davidg47]

No, I still have not been able to change the number in the report to the Month of the year. I tried the suggestions and got varied results.