440,440 Members | 1,872 Online
Need help? Post your question and get tips & solutions from a community of 440,440 IT Pros & Developers. It's quick & easy.

# CONVERT MONTHS NUMBERS TO ALPHA CHARACTERS

 P: n/a Hi All, I want to create a custom auto number in a form that looks like "CRA05001" where "CR" are fixed characters, then "A" should be for January as "B" for February and so on...(this part will change depending on the month, "05001" is the year "05" and "001" a sequential increment. I Know how to get "CR?05001" but I'm stuck on trying to convert the month number to the alpha characters. Any suggestions? Thanks in advance. This is a great group. Nov 13 '05 #1
5 Replies

 P: n/a The easiest thing I can think of is to write a function liket his: Function MonthAlpha(i As Integer) As String Select Case i Case 1 : MonthAlpha = "A" Case 2 : MonthAlpha = "B" ... Case 12 : MonthAlpha = "L" End Select End Function When you call your routine to increment you autonumber add this to your call autonumString = ... & MonthAlpha(Month(yourDate)) & ... Rich *** Sent via Developersdex http://www.developersdex.com *** Nov 13 '05 #2

 P: n/a On 15 Aug 2005 11:25:30 -0700, PCV wrote: Hi All, I want to create a custom auto number in a form that looks like "CRA05001" where "CR" are fixed characters, then "A" should be for January as "B" for February and so on...(this part will change depending on the month, "05001" is the year "05" and "001" a sequential increment. I Know how to get "CR?05001" but I'm stuck on trying to convert the month number to the alpha characters. Any suggestions? Thanks in advance. This is a great group. In VBA? ="CR" & Choose(Month(Date),"A","B", ........ ,"K","L") & Format(Date,"yy") & etc. -- Fred Please only reply to this newsgroup. I do not reply to personal email. Nov 13 '05 #3

 P: n/a It works GREAT, Rich!!!!! THANKS A 1000000 Nov 13 '05 #4

 P: n/a On Mon, 15 Aug 2005 18:45:12 GMT, Rich P wrote: Alternatively, taking advantage of the ASCII character set: Function MonthAlpha(i As Integer) As String MonthAlpha = Chr(Asc("A")+i-1)) End Function You could even use this in-line in a query. -Tom. The easiest thing I can think of is to write a function liket his:Function MonthAlpha(i As Integer) As String Select Case i Case 1 : MonthAlpha = "A" Case 2 : MonthAlpha = "B" ... Case 12 : MonthAlpha = "L" End SelectEnd FunctionWhen you call your routine to increment you autonumber add this to yourcallautonumString = ... & MonthAlpha(Month(yourDate)) & ...Rich*** Sent via Developersdex http://www.developersdex.com *** Nov 13 '05 #5

 P: n/a Now, I been trying to reset the counter's last part "001" to zero when the month or the year change. Any ideas? THANKS ALL for your answer. Nov 13 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.