Incrementing

[AJ]

this should be an easy one for the gurus out there.

I need a field that increments records in the following format 123A 3
numbers and a letter. The number increment with the same letter until 999
then they need to start all over with the next letter. Then at 999Z go back
to 001A.

Such that:

123A is followed by 124A... then 999A would be followed by 001B... then 999Z
is followed by 001A.

How would this be accomplished?
Thanks so much in advance!

 

[Jeff Boyce]

One way to look at this would be to use two fields, one for the digits, one
for the alpha character. You'd use a query to concatenate them for display
(e.g., in a form or report).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

As Jeff points out, two fields would be preferrable here.
The problem you will run into is finding the highest value for the field.
This is commonly done using the DMax function.

The first go round from 001A to 999A would work fine, then you would add
001B, but now every time you use the DMax, it will always return 999A because
that sorts higher than 001B. So, your options are to either reverse the
order to A001 or use two fields. The one for the number being a long integer
data type and the one for the letter a text data type. Then you could use a
routine something like this to create the next value in the series:

Function GetNextNumber() As String
Dim strHighLetter As String
Dim lngHighNumber As Long

'Find the current highest letter

strHighLetter = Nz(DMax("[SeqLetter]", "MyTable"),"A")

'Find the current highest number for the letter and add 1 to it

lngHighNumber = Nz(DMax("[SeqNumber]", "MyTable","[SeqHighLetter] = """
& strHighLetter & """"),0) + 1

'See if we need to advance to the next letter - I did not do any checking to
see
'if we go past Z. You need to figure out what to do and plan for it

If lngHighNumber > 999 Then
strHighLetter = Chr(Asc(strHighLetter) + 1)
lngHighNumber = 1
End If

'Combine them in a string and return the value

GetNextNumber = Format(lngHighNumber, "000") & strHighLetter
End Sub

 

[John Spencer]

This is fairly complex since after 25,974 records you are going to allow repeats.

Once you allow repeats, you have a problem identifying what the next value in
the sequence is since you now have all possible values used.

You need to identify how many sequences have been completed - Count the number
of records with 999Z.

Then you need to identify the highest number value in the latest sequence.
Then you need to identify the highest letter value for that number.

As stated elsewhere you might be best off storing the sequence in multiple
fields - 3 fields to be exact.
Loopcount
SequenceNumber
SequenceLetter

You could then display the sequenceNumber and SequenceLetter in combination
when needed.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[AJ]

I follow in theory, but where do I put this code? I'm also a little lost on
which fields are in the table? Sorry to be so dense. I follow that:

the Table is "My Table"
one filed is [SeqLetter] and another [SeqNumber] where does [SeqHighLetter]
come from? Maybe if I understand where to put the code the erst would come
clear to me. Again, sorry for being so dense.

thanks

As Jeff points out, two fields would be preferrable here.
The problem you will run into is finding the highest value for the field.
This is commonly done using the DMax function.

The first go round from 001A to 999A would work fine, then you would add
001B, but now every time you use the DMax, it will always return 999A because
that sorts higher than 001B. So, your options are to either reverse the
order to A001 or use two fields. The one for the number being a long integer
data type and the one for the letter a text data type. Then you could use a
routine something like this to create the next value in the series:

Function GetNextNumber() As String
Dim strHighLetter As String
Dim lngHighNumber As Long

'Find the current highest letter

strHighLetter = Nz(DMax("[SeqLetter]", "MyTable"),"A")

'Find the current highest number for the letter and add 1 to it

lngHighNumber = Nz(DMax("[SeqNumber]", "MyTable","[SeqHighLetter] = """
& strHighLetter & """"),0) + 1

'See if we need to advance to the next letter - I did not do any checking to
see
'if we go past Z. You need to figure out what to do and plan for it

If lngHighNumber > 999 Then
strHighLetter = Chr(Asc(strHighLetter) + 1)
lngHighNumber = 1
End If

'Combine them in a string and return the value

GetNextNumber = Format(lngHighNumber, "000") & strHighLetter
End Sub
--
Dave Hargis, Microsoft Access MVP

this should be an easy one for the gurus out there.

I need a field that increments records in the following format 123A 3
numbers and a letter. The number increment with the same letter until 999
then they need to start all over with the next letter. Then at 999Z go back
to 001A.

Such that:

123A is followed by 124A... then 999A would be followed by 001B... then 999Z
is followed by 001A.

How would this be accomplished?
Thanks so much in advance!

 

[Klatuu]

SeqNumber and SeqLetter are just field names I made up for example purposes.
You would have create your own fields and use your own naming convention.
Typically, this sort of routine would go in the form Current event and only
execute for new records. You would also need two controls on your form to
bind the fields to with the Visible properties of both set to Yes. You would
also need an unbound text box to display the combined number. Now, as John
Spencer points out, there is a fairly low limit on the total number of unique
records you can have. I would suggest rather then 3 digits, you expand it to
more digits based on how many records you expect to have in the table. Here
is a modified version using the form Current event that loads the text boxes
and uses 5 digits instead of 3:

Private Sub Form_Current()
Dim strHighLetter As String
Dim lngHighNumber As Long

If Me.NewRecord Then
'Find the current highest letter

strHighLetter = Nz(DMax("[SeqLetter]", "MyTable"),"A")

'Find the current highest number for the letter and add 1 to it

lngHighNumber = Nz(DMax("[SeqNumber]", "MyTable","[SeqHighLetter] =
"""
& strHighLetter & """"),0) + 1

'See if we need to advance to the next letter - I did not do any checking to
see
'if we go past Z. You need to figure out what to do and plan for it

If lngHighNumber > 99999 Then
strHighLetter = Chr(Asc(strHighLetter) + 1)
lngHighNumber = 1
End If

Me.txtSeqNumber = lngHighNumber
Me.txtSeqLetter = strHighLetter
'Combine them in a string and return the value

End If
End Sub

Note the two text box controls txtSeqNumber and txtSeqLetter. Those should
be the bound controls to hold the number. But to show the combined number to
the user for both new and exiting records, use an unbound control with a
control source of:
=Format(txtSeqNumber, "00000") & txtSeqLetter
--
Dave Hargis, Microsoft Access MVP

I follow in theory, but where do I put this code? I'm also a little lost on
which fields are in the table? Sorry to be so dense. I follow that:

the Table is "My Table"
one filed is [SeqLetter] and another [SeqNumber] where does [SeqHighLetter]
come from? Maybe if I understand where to put the code the erst would come
clear to me. Again, sorry for being so dense.

thanks

As Jeff points out, two fields would be preferrable here.
The problem you will run into is finding the highest value for the field.
This is commonly done using the DMax function.

The first go round from 001A to 999A would work fine, then you would add
001B, but now every time you use the DMax, it will always return 999A because
that sorts higher than 001B. So, your options are to either reverse the
order to A001 or use two fields. The one for the number being a long integer
data type and the one for the letter a text data type. Then you could use a
routine something like this to create the next value in the series:

Function GetNextNumber() As String
Dim strHighLetter As String
Dim lngHighNumber As Long

'Find the current highest letter

strHighLetter = Nz(DMax("[SeqLetter]", "MyTable"),"A")

'Find the current highest number for the letter and add 1 to it

lngHighNumber = Nz(DMax("[SeqNumber]", "MyTable","[SeqHighLetter] = """
& strHighLetter & """"),0) + 1

'See if we need to advance to the next letter - I did not do any checking to
see
'if we go past Z. You need to figure out what to do and plan for it

If lngHighNumber > 999 Then
strHighLetter = Chr(Asc(strHighLetter) + 1)
lngHighNumber = 1
End If

'Combine them in a string and return the value

GetNextNumber = Format(lngHighNumber, "000") & strHighLetter
End Sub
--
Dave Hargis, Microsoft Access MVP

this should be an easy one for the gurus out there.

I need a field that increments records in the following format 123A 3
numbers and a letter. The number increment with the same letter until 999
then they need to start all over with the next letter. Then at 999Z go back
to 001A.

Such that:

123A is followed by 124A... then 999A would be followed by 001B... then 999Z
is followed by 001A.

How would this be accomplished?
Thanks so much in advance!