Auto Count Month in Continuous Form

[Mpike926]

Hi all, thanks for reading. Ill do my best to explain.
We use our database to manage assets, one of which is I have a cashflow that shows all income and all expenses as columns across the top.
One of the columns is month number, which numbers (up to 60) the rows, for payments to be entered.
These are just numbers however, and I was looking for a way to get the name of the month to actually show instead of the number. The field is called MonthNumber, and I used the formula =MonthName(MonthNumber]) in an unbound cell, which does correctly change the number to the name of the month. However when it gets to 13, the unbound field yeilds an error, obviously because there is no 13th month. How do i fix this for records 13-60?


Thanks for the help.

[Robeen]

You will have to tell Access that 13, 25, 37 . . . etc are all January; 14, 26, 38 . . . are February . . .
A Case statement might be good for this.
Or you can create a formula that if the number is > 12 . . . then divide by 12 and get the remainder . . . till the remainder is less than 13 - and then use THAT number to determine the month.
I hope this helps!

[Missinglinq]

Go to an existing standard Module, or if need be, create one, and enter this code

Function ReturnMonthName(MNumber As Integer) As String
 
Select Case MNumber

 Case 1 To 12
  ReturnMonthName = MonthName(MNumber)

 Case Else
  ReturnMonthName = MonthName(MNumber Mod 12)

End Select
 
End Function

Then in the Control Source for your Unbound Textbox, use this

=ReturnMonthName([MonthNumber])

When you Save the Module, be sure to not name it ReturnMonthName! When a Module and a Function within the Module have the same name it confuses the Access Gnomes no end!

Linq ;0)>

[Mpike926]

Linq, the code worked great with one exception, the Month of December populated the first time, but when it comes back around to record #24, 36,48 and 60, it yields an error. THought this was strange

[Robeen]

MOD returns 0 when the number is 24, 36, 48 & 60 . . . and there isn't a month associated with 0.
You could add another Case to the Case statement:
Case 24, 36, 48, 60
MonthName(12)

[Missinglinq]

Sorry, for not getting back to you more quickly, I was away from my box most of the day! Robeen's exactly right, of course! I Didn't test it out far enough! My bad!

Replace what I gave you with

Function ReturnMonthName(MNumber As Integer) As String

Select Case MNumber
 
 Case 12, 24, 36, 48, 60
  ReturnMonthName = "December"
 
 Case Else
  ReturnMonthName = MonthName(MNumber Mod 12)

End Select
 
End Function

Linq ;0)>

[ssanfu]

How about this?

Function ReturnMonthName(MNumber As Integer) As String

   ReturnMonthName = MonthName((MNumber Mod 12) + Abs((MNumber Mod 12) = 0) * 12)

End Function

Could also be used in an unbound text box:

= MonthName((MonthNumber Mod 12) + Abs((MonthNumber Mod 12) = 0) * 12)

[Missinglinq]

Very clever hack!

Linq ;0)>

[Mpike926]

Man, you guys (or gals) are great. I just started using access, well using it with code, Ive always just done simple things in querys and what have you, but this is really my first intro to code.
Linq, the second versionw works wonders... sanfu, I tried yours which works as well, however, I need the month name to be controlled so I can bring the names over with me onto reports. Something I find terribly difficult to do if it is a calculated field.

[Missinglinq]

Glad we could help!

Good luck with your project!

Linq ;0)>

[ssanfu]

... sanfu, I tried yours which works as well, however, I need the month name to be controlled so I can bring the names over with me onto reports. Something I find terribly difficult to do if it is a calculated field.

Don't quite understand what you mean. The results are the same....

But as long as you have something you understand and can use, all's good!

Echoing Missinglinq :

Glad we could help!
Good luck with your project!