Incrementing Mixed Field

[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

 

[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

 

[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

 

[DS]

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

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

 

[DS]

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

:

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

 

[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/

 

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

 

[Guest]

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

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/

 

[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/

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/

 

[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

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/

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

 

[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/

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

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/

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

 

[DS]

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

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

 

[RobFMS]

DS

Please respond to the newsgroups so that the community can benefit.

Thanks,
Rob

--
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/

 

[RobFMS]

Just to make sure its clear, the variable "strValue" is the entire string
that contains the number and letter (i.e. "15C")

Rob

--
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/

 

[DS]

DS

Please respond to the newsgroups so that the community can benefit.

Thanks,
Rob

Huh, I thought I was in the newsgroup?
DS

 

[RobFMS]

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

--
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/

 

[DS]

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

Still working on the solution...
DS

 

[DS]

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

This actually worked pretty good!
Thanks
DS