Incrementing Mixed Field

D

DS

How do you increment a mixed field....
Lets say 7A to 7B etc..
or 15A to 15B etc...
or 178A to 178B etc...
I know that there is a MID() function but I'm umder the impression that
you have to know how many fields you have to count over. Any help
appreciated, Thanks
DS
 
G

Guest

Not knowing specifically what you are trying to do, I can't give a really
specific answer. What I can tell you is how to increment the character part.
if strTheLetter = "A" you can make it "B" with:
strTheLetter = Chr(Asc(strTheLetter) + 1)
The ASCII numbers for Upper Case A-Z is 65-90 and for Lower Case it is 97-122.
So how you approach it depends on how mixed it is. You also have to think
about what you do when you hit "Z". If it will always be a number followed
by 1 letter, that is not too hard:
If x is 123A
x=cstr(Val(left(x,len(x)-1))+1) & chr(asc(right(x,1))+1)
It is now 124B
Hope that gets you started
 
G

Graham Mandeno

The best way to do this is to use two fields for the number and the suffix,
and concatenate them in a query when required.

This also avoids the difficulty with sorting. If the value is in a single
text field, then "2A" will come after "19B" in an alphanumeric sort.

To avoid duplicates, you can create a multi-field key (perhaps the primary
key) comprising the two fields.

If you really have good reasons for NOT using two fields, then you can write
a function to pull the string apart, manipulate the parts, and reassemble
it. Here is a simple example, which increments the last letter by one and
leaves the rest of the string the same:

Function IncrementSuffix( sValue as string ) as string
IncrementSuffix = left( sValue, len(sValue)-1 ) _
& Chr( Asc(Right(sValue, 1) ) + 1 )
End Function
 
D

DS

Graham said:
The best way to do this is to use two fields for the number and the suffix,
and concatenate them in a query when required.

This also avoids the difficulty with sorting. If the value is in a single
text field, then "2A" will come after "19B" in an alphanumeric sort.

To avoid duplicates, you can create a multi-field key (perhaps the primary
key) comprising the two fields.

If you really have good reasons for NOT using two fields, then you can write
a function to pull the string apart, manipulate the parts, and reassemble
it. Here is a simple example, which increments the last letter by one and
leaves the rest of the string the sae:

Function IncrementSuffix( sValue as string ) as string
IncrementSuffix = left( sValue, len(sValue)-1 ) _
& Chr( Asc(Right(sValue, 1) ) + 1 )
End Function
Click to expand...
Thanks, This is exactly what I need. I'll give it a try and holler back
to let you know how it turned out!
thanks
DS
 
D

DS

Klatuu said:
Not knowing specifically what you are trying to do, I can't give a really
specific answer. What I can tell you is how to increment the character part.
if strTheLetter = "A" you can make it "B" with:
strTheLetter = Chr(Asc(strTheLetter) + 1)
The ASCII numbers for Upper Case A-Z is 65-90 and for Lower Case it is 97-122.
So how you approach it depends on how mixed it is. You also have to think
about what you do when you hit "Z". If it will always be a number followed
by 1 letter, that is not too hard:
If x is 123A
x=cstr(Val(left(x,len(x)-1))+1) & chr(asc(right(x,1))+1)
It is now 124B
Hope that gets you started

:
Click to expand...
Thanks, It seems I have a bunch of info at this point...time to
implment! I'll get back to the group on what the final outcome is
tommorrow. Once again Thans Everyone
DS
 
R

RobFMS

I put together the complete code that you will need. Here is a sample of the
output:

Starting value is: 23W
Next value is: 23X
Next value is: 23Y
Next value is: 23Z
Next value is: 24A
Next value is: 24B
Next value is: 24C
Next value is: 24D

The subprocedure TestProcess() is what I used as the starting point.
The starting value was toward the end so that I knew it would have to
rollover to a new number and letter.

Let me know if you find any kinks in it and also how it worked out for you.

Rob

Here's the code to do it:

Option Compare Database
Option Explicit

Private Const mstrLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Public Sub TestProcess()

Dim strValue As String
Dim x As Integer
Dim lngNumber As Long
Dim strChar As String

strValue = "23W"

Debug.Print "Starting value is: " & strValue

' display next 7 entries
For x = 1 To 7
lngNumber = GetNumericValue(strValue)
strChar = GetNextLetter(strValue)

If strChar = "A" Then
lngNumber = lngNumber + 1
End If

strValue = CStr(lngNumber) & strChar
Debug.Print "Next value is: " & strValue

Next x

End Sub

' Comment: Extract the numeric value
Public Function GetNumericValue( _
ByVal strValue As String) As Long

' The Val function stops reading the string at the first character it
can't
' recognize as part of a number. Symbols and characters that are often
' considered parts of numeric values, such as dollar signs and commas, are
' not recognized. However, the function recognizes the radix prefixes &O
' (for octal) and &H (for hexadecimal). Blanks, tabs, and linefeed
characters
' are stripped from the argument.
GetNumericValue = Val(strValue)

End Function

' Comment: Extract the character value
Public Function GetCharValue( _
ByVal strValue As String) As String

Dim strNumericValue As Long

strNumericValue = CStr(GetNumericValue(strValue))

GetCharValue = Trim(Replace(strValue, strNumericValue, "", 1, ,
vbTextCompare))

End Function


Public Function GetNextLetter( _
ByVal strValue As String) As String

Dim strCurrentChar As String
Dim strReturnValue As String
Dim intCurrentCharPosition As Integer

strCurrentChar = GetCharValue(strValue)

If strCurrentChar = "Z" Then
strReturnValue = "A"
Else
intCurrentCharPosition = InStr(1, mstrLetters, strCurrentChar,
vbTextCompare)
strReturnValue = Mid(mstrLetters, intCurrentCharPosition + 1, 1)
End If

GetNextLetter = strReturnValue

End Function



--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/
 
G

Graham Mandeno

Hi Rob

I can't imagine a situation where you would want 24A to be the natural
sequitur from 23Z. Maybe 23AA or something.

My point is that I see this as two separate entities - a number and a
suffix/subletter/revisioncode/whatever. In that respect, they belong in
separate fields. Defining an arbitrary end to the range of suffixes, which
causes the main number to increment, makes no sense to me.
 
G

Guest

What really makes no sense is hypothizing without knowing the business rules.

Graham Mandeno said:
Hi Rob

I can't imagine a situation where you would want 24A to be the natural
sequitur from 23Z. Maybe 23AA or something.

My point is that I see this as two separate entities - a number and a
suffix/subletter/revisioncode/whatever. In that respect, they belong in
separate fields. Defining an arbitrary end to the range of suffixes, which
causes the main number to increment, makes no sense to me.
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand

RobFMS said:
I put together the complete code that you will need. Here is a sample of
the output:

Starting value is: 23W
Next value is: 23X
Next value is: 23Y
Next value is: 23Z
Next value is: 24A
Next value is: 24B
Next value is: 24C
Next value is: 24D

The subprocedure TestProcess() is what I used as the starting point.
The starting value was toward the end so that I knew it would have to
rollover to a new number and letter.

Let me know if you find any kinks in it and also how it worked out for
you.

Rob

Here's the code to do it:

Option Compare Database
Option Explicit

Private Const mstrLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Public Sub TestProcess()

Dim strValue As String
Dim x As Integer
Dim lngNumber As Long
Dim strChar As String

strValue = "23W"

Debug.Print "Starting value is: " & strValue

' display next 7 entries
For x = 1 To 7
lngNumber = GetNumericValue(strValue)
strChar = GetNextLetter(strValue)

If strChar = "A" Then
lngNumber = lngNumber + 1
End If

strValue = CStr(lngNumber) & strChar
Debug.Print "Next value is: " & strValue

Next x

End Sub

' Comment: Extract the numeric value
Public Function GetNumericValue( _
ByVal strValue As String) As Long

' The Val function stops reading the string at the first character it
can't
' recognize as part of a number. Symbols and characters that are often
' considered parts of numeric values, such as dollar signs and commas,
are
' not recognized. However, the function recognizes the radix prefixes &O
' (for octal) and &H (for hexadecimal). Blanks, tabs, and linefeed
characters
' are stripped from the argument.
GetNumericValue = Val(strValue)

End Function

' Comment: Extract the character value
Public Function GetCharValue( _
ByVal strValue As String) As String

Dim strNumericValue As Long

strNumericValue = CStr(GetNumericValue(strValue))

GetCharValue = Trim(Replace(strValue, strNumericValue, "", 1, ,
vbTextCompare))

End Function


Public Function GetNextLetter( _
ByVal strValue As String) As String

Dim strCurrentChar As String
Dim strReturnValue As String
Dim intCurrentCharPosition As Integer

strCurrentChar = GetCharValue(strValue)

If strCurrentChar = "Z" Then
strReturnValue = "A"
Else
intCurrentCharPosition = InStr(1, mstrLetters, strCurrentChar,
vbTextCompare)
strReturnValue = Mid(mstrLetters, intCurrentCharPosition + 1, 1)
End If

GetNextLetter = strReturnValue

End Function



--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/
Click to expand...
Click to expand...
 
R

RobFMS

Given what little information is available and providing some reasonable
guess work at what the end result may be, we all try to provide a reasonable
solution.



--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/


Graham Mandeno said:
Hi Rob

I can't imagine a situation where you would want 24A to be the natural
sequitur from 23Z. Maybe 23AA or something.

My point is that I see this as two separate entities - a number and a
suffix/subletter/revisioncode/whatever. In that respect, they belong in
separate fields. Defining an arbitrary end to the range of suffixes,
which causes the main number to increment, makes no sense to me.
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand

RobFMS said:
I put together the complete code that you will need. Here is a sample of
the output:

Starting value is: 23W
Next value is: 23X
Next value is: 23Y
Next value is: 23Z
Next value is: 24A
Next value is: 24B
Next value is: 24C
Next value is: 24D

The subprocedure TestProcess() is what I used as the starting point.
The starting value was toward the end so that I knew it would have to
rollover to a new number and letter.

Let me know if you find any kinks in it and also how it worked out for
you.

Rob

Here's the code to do it:

Option Compare Database
Option Explicit

Private Const mstrLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Public Sub TestProcess()

Dim strValue As String
Dim x As Integer
Dim lngNumber As Long
Dim strChar As String

strValue = "23W"

Debug.Print "Starting value is: " & strValue

' display next 7 entries
For x = 1 To 7
lngNumber = GetNumericValue(strValue)
strChar = GetNextLetter(strValue)

If strChar = "A" Then
lngNumber = lngNumber + 1
End If

strValue = CStr(lngNumber) & strChar
Debug.Print "Next value is: " & strValue

Next x

End Sub

' Comment: Extract the numeric value
Public Function GetNumericValue( _
ByVal strValue As String) As Long

' The Val function stops reading the string at the first character it
can't
' recognize as part of a number. Symbols and characters that are often
' considered parts of numeric values, such as dollar signs and commas,
are
' not recognized. However, the function recognizes the radix prefixes &O
' (for octal) and &H (for hexadecimal). Blanks, tabs, and linefeed
characters
' are stripped from the argument.
GetNumericValue = Val(strValue)

End Function

' Comment: Extract the character value
Public Function GetCharValue( _
ByVal strValue As String) As String

Dim strNumericValue As Long

strNumericValue = CStr(GetNumericValue(strValue))

GetCharValue = Trim(Replace(strValue, strNumericValue, "", 1, ,
vbTextCompare))

End Function


Public Function GetNextLetter( _
ByVal strValue As String) As String

Dim strCurrentChar As String
Dim strReturnValue As String
Dim intCurrentCharPosition As Integer

strCurrentChar = GetCharValue(strValue)

If strCurrentChar = "Z" Then
strReturnValue = "A"
Else
intCurrentCharPosition = InStr(1, mstrLetters, strCurrentChar,
vbTextCompare)
strReturnValue = Mid(mstrLetters, intCurrentCharPosition + 1, 1)
End If

GetNextLetter = strReturnValue

End Function



--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/
Click to expand...
Click to expand...
 
G

Graham Mandeno

Sorry, Rob! I was not intending to be in any way critical of you or your
solution. Your code is great and you obviously put a lot of work into
devising an answer to the problem as described.

I was just musing that people often don't really think too much about their
"business rules" (good term, Klatuu :) before embarking on a project. They
might number things 1A, 1B, 2A, 2B, 2C, etc, but they don't stop to think
what it really means.
--
Cheers,
Graham M

RobFMS said:
Given what little information is available and providing some reasonable
guess work at what the end result may be, we all try to provide a
reasonable solution.



--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/


Graham Mandeno said:
Hi Rob

I can't imagine a situation where you would want 24A to be the natural
sequitur from 23Z. Maybe 23AA or something.

My point is that I see this as two separate entities - a number and a
suffix/subletter/revisioncode/whatever. In that respect, they belong in
separate fields. Defining an arbitrary end to the range of suffixes,
which causes the main number to increment, makes no sense to me.
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand

RobFMS said:
I put together the complete code that you will need. Here is a sample of
the output:

Starting value is: 23W
Next value is: 23X
Next value is: 23Y
Next value is: 23Z
Next value is: 24A
Next value is: 24B
Next value is: 24C
Next value is: 24D

The subprocedure TestProcess() is what I used as the starting point.
The starting value was toward the end so that I knew it would have to
rollover to a new number and letter.

Let me know if you find any kinks in it and also how it worked out for
you.

Rob

Here's the code to do it:

Option Compare Database
Option Explicit

Private Const mstrLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Public Sub TestProcess()

Dim strValue As String
Dim x As Integer
Dim lngNumber As Long
Dim strChar As String

strValue = "23W"

Debug.Print "Starting value is: " & strValue

' display next 7 entries
For x = 1 To 7
lngNumber = GetNumericValue(strValue)
strChar = GetNextLetter(strValue)

If strChar = "A" Then
lngNumber = lngNumber + 1
End If

strValue = CStr(lngNumber) & strChar
Debug.Print "Next value is: " & strValue

Next x

End Sub

' Comment: Extract the numeric value
Public Function GetNumericValue( _
ByVal strValue As String) As Long

' The Val function stops reading the string at the first character it
can't
' recognize as part of a number. Symbols and characters that are often
' considered parts of numeric values, such as dollar signs and commas,
are
' not recognized. However, the function recognizes the radix prefixes
&O
' (for octal) and &H (for hexadecimal). Blanks, tabs, and linefeed
characters
' are stripped from the argument.
GetNumericValue = Val(strValue)

End Function

' Comment: Extract the character value
Public Function GetCharValue( _
ByVal strValue As String) As String

Dim strNumericValue As Long

strNumericValue = CStr(GetNumericValue(strValue))

GetCharValue = Trim(Replace(strValue, strNumericValue, "", 1, ,
vbTextCompare))

End Function


Public Function GetNextLetter( _
ByVal strValue As String) As String

Dim strCurrentChar As String
Dim strReturnValue As String
Dim intCurrentCharPosition As Integer

strCurrentChar = GetCharValue(strValue)

If strCurrentChar = "Z" Then
strReturnValue = "A"
Else
intCurrentCharPosition = InStr(1, mstrLetters, strCurrentChar,
vbTextCompare)
strReturnValue = Mid(mstrLetters, intCurrentCharPosition + 1, 1)
End If

GetNextLetter = strReturnValue

End Function



--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/


How do you increment a mixed field....
Lets say 7A to 7B etc..
or 15A to 15B etc...
or 178A to 178B etc...
I know that there is a MID() function but I'm umder the impression that
you have to know how many fields you have to count over. Any help
appreciated, Thanks
DS
Click to expand...
Click to expand...
Click to expand...
 
R

RobFMS

No harm Graham =)



--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/


Graham Mandeno said:
Sorry, Rob! I was not intending to be in any way critical of you or your
solution. Your code is great and you obviously put a lot of work into
devising an answer to the problem as described.

I was just musing that people often don't really think too much about
their "business rules" (good term, Klatuu :) before embarking on a
project. They might number things 1A, 1B, 2A, 2B, 2C, etc, but they don't
stop to think what it really means.
--
Cheers,
Graham M

RobFMS said:
Given what little information is available and providing some reasonable
guess work at what the end result may be, we all try to provide a
reasonable solution.



--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/


Graham Mandeno said:
Hi Rob

I can't imagine a situation where you would want 24A to be the natural
sequitur from 23Z. Maybe 23AA or something.

My point is that I see this as two separate entities - a number and a
suffix/subletter/revisioncode/whatever. In that respect, they belong in
separate fields. Defining an arbitrary end to the range of suffixes,
which causes the main number to increment, makes no sense to me.
--
Cheers,

Graham Mandeno [Access MVP]
Auckland, New Zealand

I put together the complete code that you will need. Here is a sample of
the output:

Starting value is: 23W
Next value is: 23X
Next value is: 23Y
Next value is: 23Z
Next value is: 24A
Next value is: 24B
Next value is: 24C
Next value is: 24D

The subprocedure TestProcess() is what I used as the starting point.
The starting value was toward the end so that I knew it would have to
rollover to a new number and letter.

Let me know if you find any kinks in it and also how it worked out for
you.

Rob

Here's the code to do it:

Option Compare Database
Option Explicit

Private Const mstrLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Public Sub TestProcess()

Dim strValue As String
Dim x As Integer
Dim lngNumber As Long
Dim strChar As String

strValue = "23W"

Debug.Print "Starting value is: " & strValue

' display next 7 entries
For x = 1 To 7
lngNumber = GetNumericValue(strValue)
strChar = GetNextLetter(strValue)

If strChar = "A" Then
lngNumber = lngNumber + 1
End If

strValue = CStr(lngNumber) & strChar
Debug.Print "Next value is: " & strValue

Next x

End Sub

' Comment: Extract the numeric value
Public Function GetNumericValue( _
ByVal strValue As String) As Long

' The Val function stops reading the string at the first character it
can't
' recognize as part of a number. Symbols and characters that are often
' considered parts of numeric values, such as dollar signs and commas,
are
' not recognized. However, the function recognizes the radix prefixes
&O
' (for octal) and &H (for hexadecimal). Blanks, tabs, and linefeed
characters
' are stripped from the argument.
GetNumericValue = Val(strValue)

End Function

' Comment: Extract the character value
Public Function GetCharValue( _
ByVal strValue As String) As String

Dim strNumericValue As Long

strNumericValue = CStr(GetNumericValue(strValue))

GetCharValue = Trim(Replace(strValue, strNumericValue, "", 1, ,
vbTextCompare))

End Function


Public Function GetNextLetter( _
ByVal strValue As String) As String

Dim strCurrentChar As String
Dim strReturnValue As String
Dim intCurrentCharPosition As Integer

strCurrentChar = GetCharValue(strValue)

If strCurrentChar = "Z" Then
strReturnValue = "A"
Else
intCurrentCharPosition = InStr(1, mstrLetters, strCurrentChar,
vbTextCompare)
strReturnValue = Mid(mstrLetters, intCurrentCharPosition + 1, 1)
End If

GetNextLetter = strReturnValue

End Function



--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/


How do you increment a mixed field....
Lets say 7A to 7B etc..
or 15A to 15B etc...
or 178A to 178B etc...
I know that there is a MID() function but I'm umder the impression
that you have to know how many fields you have to count over. Any
help appreciated, Thanks
DS
Click to expand...
Click to expand...
Click to expand...
 
D

DS

RobFMS said:
I put together the complete code that you will need. Here is a sample of the
output:

Starting value is: 23W
Next value is: 23X
Next value is: 23Y
Next value is: 23Z
Next value is: 24A
Next value is: 24B
Next value is: 24C
Next value is: 24D

The subprocedure TestProcess() is what I used as the starting point.
The starting value was toward the end so that I knew it would have to
rollover to a new number and letter.

Let me know if you find any kinks in it and also how it worked out for you.

Rob

Here's the code to do it:

Option Compare Database
Option Explicit

Private Const mstrLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Public Sub TestProcess()

Dim strValue As String
Dim x As Integer
Dim lngNumber As Long
Dim strChar As String

strValue = "23W"

Debug.Print "Starting value is: " & strValue

' display next 7 entries
For x = 1 To 7
lngNumber = GetNumericValue(strValue)
strChar = GetNextLetter(strValue)

If strChar = "A" Then
lngNumber = lngNumber + 1
End If

strValue = CStr(lngNumber) & strChar
Debug.Print "Next value is: " & strValue

Next x

End Sub

' Comment: Extract the numeric value
Public Function GetNumericValue( _
ByVal strValue As String) As Long

' The Val function stops reading the string at the first character it
can't
' recognize as part of a number. Symbols and characters that are often
' considered parts of numeric values, such as dollar signs and commas, are
' not recognized. However, the function recognizes the radix prefixes &O
' (for octal) and &H (for hexadecimal). Blanks, tabs, and linefeed
characters
' are stripped from the argument.
GetNumericValue = Val(strValue)

End Function

' Comment: Extract the character value
Public Function GetCharValue( _
ByVal strValue As String) As String

Dim strNumericValue As Long

strNumericValue = CStr(GetNumericValue(strValue))

GetCharValue = Trim(Replace(strValue, strNumericValue, "", 1, ,
vbTextCompare))

End Function


Public Function GetNextLetter( _
ByVal strValue As String) As String

Dim strCurrentChar As String
Dim strReturnValue As String
Dim intCurrentCharPosition As Integer

strCurrentChar = GetCharValue(strValue)

If strCurrentChar = "Z" Then
strReturnValue = "A"
Else
intCurrentCharPosition = InStr(1, mstrLetters, strCurrentChar,
vbTextCompare)
strReturnValue = Mid(mstrLetters, intCurrentCharPosition + 1, 1)
End If

GetNextLetter = strReturnValue

End Function
Click to expand...
Wow this is awesome! I'll give it a try. The situation is this. This
for a restaurant. Whenever anyone wants to split a check I need the
table number (The Number to stay the same) and add a letter to it.
Going up to Z is fine. I've never seen a situation where someone needs
more than 26 seperate checks! I'll give it a try and let you know. Thanks
DS
 
D

DS

RobFMS said:
DS

It is.... but just wanted to make sure that when you said you would get back
to me on how the solution worked for you, I wanted to make sure that you put
the information here on the newsgroup and not to my personal email account.

Rob
Click to expand...

Still working on the solution...
DS
 
D

DS

Graham said:
The best way to do this is to use two fields for the number and the suffix,
and concatenate them in a query when required.

This also avoids the difficulty with sorting. If the value is in a single
text field, then "2A" will come after "19B" in an alphanumeric sort.

To avoid duplicates, you can create a multi-field key (perhaps the primary
key) comprising the two fields.

If you really have good reasons for NOT using two fields, then you can write
a function to pull the string apart, manipulate the parts, and reassemble
it. Here is a simple example, which increments the last letter by one and
leaves the rest of the string the same:

Function IncrementSuffix( sValue as string ) as string
IncrementSuffix = left( sValue, len(sValue)-1 ) _
& Chr( Asc(Right(sValue, 1) ) + 1 )
End Function
Click to expand...
This actually worked pretty good!
Thanks
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Incremental Numbering 5
Limiting Field Input to 1/8 Increments 2
Autopopulating a yes/no box 1
Incrementing Number 22
Incremental number 2
incrementing record number 5
Increment Alpha-characters 3
Auto Increment Field or Identity Field In MS SQL Server 0

Top