By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,440 Members | 1,872 Online
Bytes IT Community
+ Ask a Question
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
PCV
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
Share this Question
Share on Google+
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
PCV
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 <rp*****@aol.com> 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 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 #5

P: n/a
PCV
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.