Tricky auto numbering question

[AJ]

I have a form with a blank textbox [RMA Number]. I also have a button
with which I want to generate a unique, sequential number in the
textbox.

The tricky part is that the format of the number is a value that must
follow this format:

05K025

Where "05" is the current two digit year, "K" is the current month
represented by a letter, eg: January = A, Feb = B..etc. and "025" is a
number that increases sequentially with each new record.

What's more, I need that "025" to reset to 001 when a new month starts,
eg:

05L01 = first RMA issued during the month of december, 2005.

I'm thinking I need to implement code like this for the month:

Function DateDiffM(BegDate, EndDate)
Const JANUARY = A
Const DECEMBER = L
Dim NumWeeks As String

But I'm not sure what to do with it all. It's got me stumped. Anbody
have any revelations?

Thanks!

 

[Douglas J. Steele]

Realistically, what you're trying to do isn't a particularly good idea.
You're trying to stuff 3 separate pieces of information into a single field.
Better is to use 3 separate fields (or, since Year and Month are presumably
related to something like TransactionDate, 2 separate fields). You can
create a concatenated key like that in a query, and use that query wherever
you would otherwise have used the table.