Unique number

[Guest]

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.

 

[Joseph Meehan]

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 fixed letters is not stored in the table. You display it when you
want to see it on forms and reports. No need to store it in the table.

The

number will depend on the month and the letter on the year. For
example:

You also don't store data that can be computed. In fact I would suggest
storing the numbers 123 and or ABC in the table and then displaying the year
or month name as desired in the form or report as needed. You set up a look
up table for the values to display related to the primary data table.

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.

For the incremental number take a look at:

http://www.lebans.com/rownumber.htm

I have not worked with incremental numbers, but I know what you want can
be done. It that reference does not help there are ways.

 

[BruceM]

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.

 

[Guest]

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.

 

[BruceM]

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).

 

[Guest]

Hi

The "ConcatNumber" - is this the name of the field in the table or the name
of a text box on the Form?
--
Thank you for your help

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).

 

[BruceM]

From the previous post: "...assumes that the table is named tblConcat, and
that the field in which the value is stored is ConcatNumber"

If you drag a field from the field list to the form design view it will give
a text box the same name as the field, in which case you should change the
text box name to something like txtConcatNumber.

Hi

The "ConcatNumber" - is this the name of the field in the table or the
name
of a text box on the Form?
--
Thank you for your help

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).

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.

 

[Guest]

When I run the Form I get the error:

Run-time error '94'

Invalid use of Null

And the VB Code that is in yellow is:
strWhere = "BNSBatchNo Like """ & Format(Date, "m") & _
Chr(Right(Year(Date), 2) + 58) & "*"""

--
Thank you for your help

From the previous post: "...assumes that the table is named tblConcat, and
that the field in which the value is stored is ConcatNumber"

If you drag a field from the field list to the form design view it will give
a text box the same name as the field, in which case you should change the
text box name to something like txtConcatNumber.

Hi

The "ConcatNumber" - is this the name of the field in the table or the
name
of a text box on the Form?
--
Thank you for your help

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).

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.

 

[BruceM]

Do you have a field named Date, Year, or Right? If so, you should change
the name. Those are reserved words in Access, so the code may be getting
confused.

When I run the Form I get the error:

Run-time error '94'

Invalid use of Null

And the VB Code that is in yellow is:
strWhere = "BNSBatchNo Like """ & Format(Date, "m") & _
Chr(Right(Year(Date), 2) + 58) & "*"""

--
Thank you for your help

From the previous post: "...assumes that the table is named tblConcat,
and
that the field in which the value is stored is ConcatNumber"

If you drag a field from the field list to the form design view it will
give
a text box the same name as the field, in which case you should change
the
text box name to something like txtConcatNumber.

Hi

The "ConcatNumber" - is this the name of the field in the table or the
name
of a text box on the Form?
--
Thank you for your help


:

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).

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.