I'll probably get in trouble for suggesting this approach. If you have a
date field in your table (the date the record was created) it should be
possible to increment just the number at the end, and to combine it with
the
year and the month from the date field, and to have the number start over
each month, but I can't seem to work out how to do that. I probably
could
if I spent more time on it, but something about it eludes me at the
moment.
My approach involves storing parts of the current date. Many will argue
that this shouldn't be done, ever.
Having said that, and with the caveat that many experienced developers
would
take a different approach, here's a way to accomplish what you need. The
following code goes into the form's Current event. It assumes that the
table is named tblConcat, and that the field in which the value is stored
is
ConcatNumber. Substitute the names you are actually using.
Private Sub Form_Current()
If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant
strWhere = "ConcatNumber Like """ & Format(Date, "m") & _
Chr(Right(Year(Date), 2) + 58) & "*"""
varResult = DMax("ConcatNumber", "tblConcat", strWhere)
If IsNull(varResult) Then
Me.ConcatNumber = Format(Date, "m") & _
Chr(Right(Year(Date), 2) + 58) & "1"
Else
Me.ConcatNumber = Left(varResult, 2) & _
Format(Right(varResult, 1) + 1, "0")
End If
End If
End Sub
To add the BNS, you could use the following as the Control Source of an
unbound text box on a form or report:
= "BNS" & [ConcatNumber]
Similarly, you could base the form or report on a query, and use the same
expression in a slightly different arrangement as the value in a
calculated
field (a blank column in query design view):
CompleteNumber: "BNS" & [ConcatNumber]
Again, use whatever name you choose for the calculated field. As always,
it's best to avoid spaces and to limit the name to alphanumeric
characters
and underscores. This applies to all naming.
You would do well to open the immediate window by pressing Ctrl + G from
within Access, and experiment with the functions.
?Year(Date) in the immediate window will give you the current year.
?Right(Year(Date),2) will give you the rightmost two digits from the
current
year.
?Chr(65) will give you A. By adding 58 to the rightmost digits from the
year you produce the equivalent of Chr(65). The Chr function returns a
character for a digit up to 255. Chr(66) is B, so next year you will be
adding 58 to 08 to produce the equivalent of Chr(66).
Kirt84 said:
Hi Bruce
Where do i place this code? And how do I get the 'BNS' at the begining
of
the code. At the moment i'm placing it on the form and only getting an
'A'
in
the text box.
--
Thank you for your help
:
You could use the following to get the letter you need for the year:
Chr(Right(Year(Date()),2) + 58)
I should mention that the database to which you referred in the other
post
seems to be for numbering rows on a form or report on the fly, not for
storing a record number.
I want to have a unique identification number that will be called our
internal batch number. I want it to consist of 3 fixed letters, a
number,
letter and auto number. The fixed letters will be 'BNS'. The number
will
depend on the month and the letter on the year. For example:
1 = Jan
2 = Feb
3 = Mar
4 = Apr
5 = May
Etc.
A = 2007
B = 2008
C = 2009
D = 2010
Etc.
So if this started today then the batch number will be BNS3A1 then
it
will
carry on as BNS3A2, BNS3A3, BNS3A4 etc. Is this possible to do? I
don't
mind
going in every month and changing the month and year, as long as the
auto
number works.