Need help parsing a string - reverse Val?

[google]

I'm trying to implement something that would speed up data entry. I'd
like to be able to take a string, and increment ONLY the right-most
numerical characters by one. The type structure of the data that is in
this field can vary. It's a list of mechanical equipment, and how it
is designated varies based on how the customer has them labeled. For
example, a list of their equipment might look like:

CH-1
CH-2
CH-3

or it might be

AH-1-1
AH-1-2
AH-1-3

or even

FC-1-1G
FC-1-2G
FC-1-3G

And there will be times where it's even more obscure, but I'd say at
least 75% of the time, the right-most numerical part of the string will
increment like above. It seems that what I need is to somehow get the
Val function to read from right to left (it does stop at dashes,
right?), pull out that number, increment it, then shove it back in the
string. I already have my form set up so that the field automatically
populates with the value from the previous record (so they only have to
go in & edit the number to be correct instead of typing the entire
string), but what I'd like to do is also implent code on a double-click
that will take the value of that field, and do the above incrementing
(which would have the added advantage of making it easy when units are
skipped... just double-click twice).

Any ideas?

Thanks!

[Terry Kreft]

This seems towork

Function IncrLast(RHS As String) as String
Dim intInstr As Integer
Dim strLeft As String
Dim strRight As String
Dim strAlpha As String
Dim intNumber As Integer

intInstr = InStrRev(RHS, "-", Compare:=vbBinaryCompare)

strLeft = left(RHS, intInstr)
strRight = Mid(RHS, intInstr + 1)
intNumber = Val(strRight)
strAlpha = Mid(strRight, Len(intNumber))
intNumber = intNumber + 1

IncrLast = strLeft & intNumber & strAlpha
End Function

Test values
?IncrLast("CH-1")
CH-2
?IncrLast("AH-1-1")
AH-1-2
?IncrLast("FC-1-1G")
FC-1-2G

--
Terry Kreft
MVP Microsoft Access
<go****@darincline.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I'm trying to implement something that would speed up data entry. I'd
like to be able to take a string, and increment ONLY the right-most
numerical characters by one. The type structure of the data that is in
this field can vary. It's a list of mechanical equipment, and how it
is designated varies based on how the customer has them labeled. For
example, a list of their equipment might look like:

CH-1
CH-2
CH-3

or it might be

AH-1-1
AH-1-2
AH-1-3

or even

FC-1-1G
FC-1-2G
FC-1-3G

And there will be times where it's even more obscure, but I'd say at
least 75% of the time, the right-most numerical part of the string will
increment like above. It seems that what I need is to somehow get the
Val function to read from right to left (it does stop at dashes,
right?), pull out that number, increment it, then shove it back in the
string. I already have my form set up so that the field automatically
populates with the value from the previous record (so they only have to
go in & edit the number to be correct instead of typing the entire
string), but what I'd like to do is also implent code on a double-click
that will take the value of that field, and do the above incrementing
(which would have the added advantage of making it easy when units are
skipped... just double-click twice).

Any ideas?

Thanks!

[google]

Excellent! First pass seems to work, EXCEPT once you get to 10. It
wants to make the next number 110. In fact, it starts to fail on
anything more than single digit numbers. I'm not very good at all
these functions, I need to look them all up to see what they do to see
if I can understand what's happening.

[google]

Ok, now that I see what InStrRev does, that may be part of the problem
(and will be for other formats that don't use a "-", like AHU1. I
assume that the defined position of the dash is what's causing the
numbers to grow once the numeric portion that is being incremented gets
past a single digit. I'll have to digest some more.

[google]

Hmmm, I'm still having trouble. I took yours, then re-worked it with a
different approach (some unneeded declarations are still in there):
Private Function IncrLast(RHS As String) As String
Dim intInstr As Integer
Dim strLeft As String
Dim strRight As String
Dim strAlpha As String
Dim intNumber As Integer
Dim revString As String
Dim revNumber As Integer
Dim revNumStr As String
Dim revNewNumber As String
Dim NumLength As Integer
Dim NumPos As Integer

revString = StrReverse(RHS) ' reverse the string
revNumber = Val(revString) ' get the number out of the string
revNumStr = CStr(revNumber) ' convert the reversed number to a string
intNumber = CInt(StrReverse(revNumStr)) ' reverse the string back to
normal and convert to number
intNumber = intNumber + 1 ' Increment the number
revNewNumber = StrReverse(CStr(intNumber)) ' reverse the incremented
number as a string
NumLength = Len(revNumStr) 'length in digits of initial number
NumPos = InStr(revString, revNumStr)
strLeft = Left(revString, NumPos - 1)
strRight = Mid(revString, NumPos + NumLength)
strAlpha = strLeft & revNewNumber & strRight
IncrLast = StrReverse(strAlpha)
End Function

In essence, I'm reversing the string FIRST, so I can use Val to figure
out what the first (or actually last) sequence of characters that are a
number, and not rely on a dash. I then take the number, increment it,
put it all back together, then reverse it back. So far, I've
discovered two problems:

First off, when I reverse my string, AHU-20, for example, becomes
02-UHA. Val only returns 2, because it's discarding the leading 0. I
then tried it with a more complex string: AHU-2-5b. The reversed
version is b5-2-UHA, and I would THINK that Val on this would return 5,
but it's returning 0. Why is that?

I understand why the first thing is happening (but don't know how to
get around it), the second thing has me stumped!

[standup101011]

You might try padding Len(intNumber) with a string character:

Change in the existing code

strAlpha = Mid(strRight, Len(intNumber))

To

strAlpha = Mid(strRight, Len("A" & intNumber))

HTH

[bruce]

go****@darincline.com wrote:

I'm trying to implement something that would speed up data entry. I'd
like to be able to take a string, and increment ONLY the right-most
numerical characters by one. The type structure of the data that is in
this field can vary. It's a list of mechanical equipment, and how it
is designated varies based on how the customer has them labeled. For
example, a list of their equipment might look like:

CH-1
CH-2
CH-3

or it might be

AH-1-1
AH-1-2
AH-1-3

or even

FC-1-1G
FC-1-2G
FC-1-3G

And there will be times where it's even more obscure, but I'd say at
least 75% of the time, the right-most numerical part of the string will
increment like above. It seems that what I need is to somehow get the
Val function to read from right to left (it does stop at dashes,
right?), pull out that number, increment it, then shove it back in the
string. I already have my form set up so that the field automatically
populates with the value from the previous record (so they only have to
go in & edit the number to be correct instead of typing the entire
string), but what I'd like to do is also implent code on a double-click
that will take the value of that field, and do the above incrementing
(which would have the added advantage of making it easy when units are
skipped... just double-click twice).

This does the trick for me (and lookie! No Val() function!):

Function IncNum(str As String) As String

Dim intNumStart As Integer
Dim intNumLen As Integer
Dim i As Integer

For i = Len(str) To 1 Step -1
If IsNumeric(Mid(str, i, 1)) Then
intNumLen = intNumLen + 1
intNumStart = i
Else
If intNumLen > 0 Then Exit For
End If
Next i

IncNum = Mid(str, 1, intNumStart - 1) & CLng(Mid(str, intNumStart,
intNumLen)) + 1 & Mid(str, intNumStart + intNumLen)

End Function

HTH,
Bruce

[ByteMyzer]

<go****@darincline.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Hmmm, I'm still having trouble. I took yours, then re-worked it with a
different approach (some unneeded declarations are still in there):
Private Function IncrLast(RHS As String) As String
Dim intInstr As Integer
Dim strLeft As String
Dim strRight As String
Dim strAlpha As String
Dim intNumber As Integer
Dim revString As String
Dim revNumber As Integer
Dim revNumStr As String
Dim revNewNumber As String
Dim NumLength As Integer
Dim NumPos As Integer

revString = StrReverse(RHS) ' reverse the string
revNumber = Val(revString) ' get the number out of the string
revNumStr = CStr(revNumber) ' convert the reversed number to a string
intNumber = CInt(StrReverse(revNumStr)) ' reverse the string back to
normal and convert to number
intNumber = intNumber + 1 ' Increment the number
revNewNumber = StrReverse(CStr(intNumber)) ' reverse the incremented
number as a string
NumLength = Len(revNumStr) 'length in digits of initial number
NumPos = InStr(revString, revNumStr)
strLeft = Left(revString, NumPos - 1)
strRight = Mid(revString, NumPos + NumLength)
strAlpha = strLeft & revNewNumber & strRight
IncrLast = StrReverse(strAlpha)
End Function

In essence, I'm reversing the string FIRST, so I can use Val to figure
out what the first (or actually last) sequence of characters that are a
number, and not rely on a dash. I then take the number, increment it,
put it all back together, then reverse it back. So far, I've
discovered two problems:

First off, when I reverse my string, AHU-20, for example, becomes
02-UHA. Val only returns 2, because it's discarding the leading 0. I
then tried it with a more complex string: AHU-2-5b. The reversed
version is b5-2-UHA, and I would THINK that Val on this would return 5,
but it's returning 0. Why is that?

I understand why the first thing is happening (but don't know how to
get around it), the second thing has me stumped!

Try:
'----------<begin code>----------
Function IncrLast(ByVal RHS As String) As String
Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
i2 = Len(RHS)
Do While Not IsNumeric(Mid(RHS, i2, 1))
i2 = i2 - 1
Loop
i1 = i2
Do While IsNumeric(Mid(RHS, i1, 1)) And (Mid(RHS, i1, 1) <> "-")
i1 = i1 - 1
Loop
i3 = CInt(Mid(RHS, i1 + 1, i2 - i1)) + 1
IncrLast = Left(RHS, i1) & CStr(format(i3, "0")) & Mid(RHS, i2 + 1)
End Function

'-----------<end code>-----------

[John Nurick]

On 30 Aug 2005 14:35:37 -0700, br***@aristotle.net wrote:

This does the trick for me (and lookie! No Val() function!):

Bruce's function fails if there's no number in the string, and also
loses any leading zeros (i.e. AB001 increments to AB2). How about this
(variant argument and return type used so it can easily be called from
a query):

Function IncNum2(V As Variant) As Variant
Dim oRE As Object 'VBScript_RegExp_55.RegExp
Dim oMatches As Object 'VBScript_RegExp_55.MatchCollection
Dim strN As String
Dim lngLenDiff As Long

If IsNull(V) Then Exit Function

Set oRE = CreateObject("VBScript.RegExp")
oRE.Pattern = "(.*?)(\d+)(\D*)$"
Set oMatches = oRE.Execute(CStr(V))
If oMatches.Count = 0 Then 'can't increment
IncNum2 = V
Else
With oMatches(0)
strN = CStr(CLng(.SubMatches(1)) + 1) 'increment
'restore any leading zeroes
lngLenDiff = Len(.SubMatches(1)) - Len(strN)
If lngLenDiff > 0 Then
strN = String(lngLenDiff, "0") & strN
End If
IncNum2 = .SubMatches(0) & strN & .SubMatches(2)
End With
End If
End Function


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

[rkc]

John Nurick wrote:

On 30 Aug 2005 14:35:37 -0700, br***@aristotle.net wrote:

This does the trick for me (and lookie! No Val() function!):

Bruce's function fails if there's no number in the string, and also
loses any leading zeros (i.e. AB001 increments to AB2). How about this
(variant argument and return type used so it can easily be called from
a query):

Function IncNum2(V As Variant) As Variant
Dim oRE As Object 'VBScript_RegExp_55.RegExp
Dim oMatches As Object 'VBScript_RegExp_55.MatchCollection
Dim strN As String
Dim lngLenDiff As Long

If IsNull(V) Then Exit Function

Set oRE = CreateObject("VBScript.RegExp")
oRE.Pattern = "(.*?)(\d+)(\D*)$"
Set oMatches = oRE.Execute(CStr(V))
If oMatches.Count = 0 Then 'can't increment
IncNum2 = V
Else
With oMatches(0)
strN = CStr(CLng(.SubMatches(1)) + 1) 'increment
'restore any leading zeroes
lngLenDiff = Len(.SubMatches(1)) - Len(strN)
If lngLenDiff > 0 Then
strN = String(lngLenDiff, "0") & strN
End If
IncNum2 = .SubMatches(0) & strN & .SubMatches(2)
End With
End If
End Function

Fails if zero is the first character and a leading zero is in fact
required to be preserved.

099C-FF-A -> 100C-FF-A

[Terry Kreft]

Me being stupid shoudl be

Function IncrLast(RHS As String)
Dim intInstr As Integer
Dim strLeft As String
Dim strRight As String
Dim strAlpha As String
Dim intNumber As Integer

intInstr = InStrRev(RHS, "-", Compare:=vbBinaryCompare)

strLeft = left(RHS, intInstr)
strRight = Mid(RHS, intInstr + 1)
intNumber = Val(strRight)
strAlpha = Mid(strRight, Len(CStr(intNumber)) + 1) ' Note difference here
intNumber = intNumber + 1

IncrLast = strLeft & intNumber & strAlpha
End Function
--
Terry Kreft
MVP Microsoft Access
<go****@darincline.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Excellent! First pass seems to work, EXCEPT once you get to 10. It
wants to make the next number 110. In fact, it starts to fail on
anything more than single digit numbers. I'm not very good at all
these functions, I need to look them all up to see what they do to see
if I can understand what's happening.

[Malcolm Cook]

hows this?

Public Function IncTrailingNumeric(s As String) As String
'PURPOSE: increment the last numeric portion of input string\
'METHOD: regular expressions are your friend!
'REQUIRES: reference to ''Microsoft VBScript Regular Expressions 5.5'
'TODO: replace asserts with error raise
'EXAMPLE I/O: (past the print statement into the immediate window)
'Print IncTrailingNumeric("CH-1")
'CH-2
'Print IncTrailingNumeric("AH-1-1")
'AH-1-2
'Print IncTrailingNumeric("FC-1-1G")
'FC-1-2G
'Print IncTrailingNumeric("FC-1-19\G")
'FC-1-20\G
Dim re As New RegExp
Dim Matches As MatchCollection
Dim m As Match

With re
.Pattern = "(.*\D)(\d+)(\D*)"
Set Matches = re.Execute(s)
Debug.Assert Not Matches Is Nothing
Debug.Assert Matches.Count = 1
Set m = Matches(0)
IncTrailingNumeric = m.SubMatches(0) & (1 + Val(m.SubMatches(1))) & m.SubMatches(2)

End With
End Function

[Albert D.Kallal]

Well, we don't really want to add a reference to make this run...do we?
(but, nice solution by the way)

However, what about

"AH-1-G1"
"AH-1-G2"
"AH-1-G3"

A good number of solutions posted allow text on EITHER side of the number.

Without a doubt, this is GREAT little problem. Once I might keep for
interviewing new developers.....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[google]

Wow, thanks for all the help everyone! I was able to streamline pretty
much everything else but this field, which was the only one left that
required any mentionable keystrokes that had any potential of
automating. This will really help them fly through this application!

[Malcolm Cook]

Albert,

The function I coded handles your sequence just fine. input "AH-1-G1" output "AH-1-G2". n'est pas?

I use regexp all the time and thing they should be built into the language.... so, I have no problem with 'adding a reference'
since I almost always add it.

Except... the problem with adding a reference is a pain for me when i go to distibute my Access app since the Mcirosoft Access
developer extensions for packaging an app barf (technical term) on selected dlls, especiall ones which have a number, like this:

Microsoft VBScript Regular Expressions 5.5
Location: C:\WINDOWS\system32\vbscript.dll\3
Language: Standard
Regards,

Malcolm

[Douglas J. Steele]

You could always use Late Binding, so that no reference is required:

Public Function IncTrailingNumeric(s As String) As String
Dim re As Object
Dim Matches As Object
Dim m As Object

Set re = CreateObject("VBScript.RegExp")
With re
.Pattern = "(.*\D)(\d+)(\D*)"
Set Matches = re.Execute(s)
Debug.Assert Not Matches Is Nothing
Debug.Assert Matches.Count = 1
Set m = Matches(0)
IncTrailingNumeric = m.SubMatches(0) & (1 + Val(m.SubMatches(1))) &
m.SubMatches(2)
End With
End Function

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Malcolm Cook" <ma*****@newsgroup.nospam> wrote in message
news:uM**************@tk2msftngp13.phx.gbl...

Albert,

The function I coded handles your sequence just fine. input "AH-1-G1"
output "AH-1-G2". n'est pas?

I use regexp all the time and thing they should be built into the
language.... so, I have no problem with 'adding a reference' since I
almost always add it.

Except... the problem with adding a reference is a pain for me when i go
to distibute my Access app since the Mcirosoft Access developer extensions
for packaging an app barf (technical term) on selected dlls, especiall
ones which have a number, like this:

Microsoft VBScript Regular Expressions 5.5
Location: C:\WINDOWS\system32\vbscript.dll\3
Language: Standard
Regards,

Malcolm

[John Nurick]

On Thu, 1 Sep 2005 15:15:46 -0500, "Malcolm Cook"
<ma*****@newsgroup.nospam> wrote:

The function I coded handles your sequence just fine. input "AH-1-G1" output "AH-1-G2". n'est pas?

The one thing it doesn't seem to handle is leading zeros, e.g. input
"AB001", output "AB2" (unless you count the trivial case where the
string contains only digits).
--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

[qvb jat vke]

"John Nurick" <j.*************@dial.pipex.com>
??????:oa********************************@4ax.com. ..

On Thu, 1 Sep 2005 15:15:46 -0500, "Malcolm Cook"
<ma*****@newsgroup.nospam> wrote:

The function I coded handles your sequence just fine. input "AH-1-G1"

output "AH-1-G2". n'est pas?
The one thing it doesn't seem to handle is leading zeros, e.g. input
"AB001", output "AB2" (unless you count the trivial case where the
string contains only digits).
--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

[John Griffiths]

Rather than let a good thread die how about :-

Function IncNum(str As String) As String
'--------------------------------
IncNum = ""

If Len(str) < 1 Then
Exit Function
End If
'--------------------------------
Dim Result As String

Dim re As New RegExp
Dim ms As MatchCollection
Dim m As Match

Dim Digits As String
Dim Incr As Long
Dim i As Long
'--------------------------------
Result = str

re.Pattern = "([0-9]+)"
re.Global = True
re.IgnoreCase = True
Set ms = re.Execute(Result)

Incr = 1
'--------------------------------
For i = ms.Count - 1 To 0 Step -1
Set m = ms(i)

Digits = m.Value
strFormat = String(m.Length, "0")

Digits = Format(Digits + Incr, strFormat)
If Len(Digits) > m.Length Then
Digits = Mid(Digits, 2)
Else
Incr = 0
End If

Mid(Result, m.FirstIndex + 1, m.Length) = Digits

Next 'm
'--------------------------------
IncNum = Result

If Incr > 0 Then
Err.Raise 5, Err.Source, "Unable to increment code string."
End If
'--------------------------------
End Function

Requires reference to ms regular expressions 1.0 or 5.5

Try
?IncNum("000asd999.999.999")

Anyone keeping count of the ways?

Regards John

"qvb jat vke" <qv*******@msn.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl...

"John Nurick" <j.*************@dial.pipex.com>
??????:oa********************************@4ax.com. ..

On Thu, 1 Sep 2005 15:15:46 -0500, "Malcolm Cook"
<ma*****@newsgroup.nospam> wrote:

The function I coded handles your sequence just fine. input "AH-1-G1"

output "AH-1-G2". n'est pas?

The one thing it doesn't seem to handle is leading zeros, e.g. input
"AB001", output "AB2" (unless you count the trivial case where the
string contains only digits).
--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

[Douglas J. Steele]

As I pointed out elsewhere in this thread, you don't have to have a
reference to MS Regular Expressions to use regular expressions: you can use
Late Binding. (Note that you're missing a declaration for strFormat)

Function IncNum(str As String) As String
'--------------------------------
IncNum = ""

If Len(str) < 1 Then
Exit Function
End If
'--------------------------------
Dim Result As String

Dim re As Object
Dim ms As Object
Dim m As Object

Dim Digits As String
Dim Incr As Long
Dim i As Long
Dim strFormat As String
'--------------------------------
Result = str

Set re = CreateObject("VBScript.RegExp")
re.Pattern = "([0-9]+)"
re.Global = True
re.IgnoreCase = True
Set ms = re.Execute(Result)

Incr = 1
'--------------------------------
For i = ms.Count - 1 To 0 Step -1
Set m = ms(i)

Digits = m.Value
strFormat = String(m.Length, "0")

Digits = Format(Digits + Incr, strFormat)
If Len(Digits) > m.Length Then
Digits = Mid(Digits, 2)
Else
Incr = 0
End If

Mid(Result, m.FirstIndex + 1, m.Length) = Digits

Next 'm
'--------------------------------
IncNum = Result

If Incr > 0 Then
Err.Raise 5, Err.Source, "Unable to increment code string."
End If
'--------------------------------
End Function
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"John Griffiths" <re***@to.group.only.com> wrote in message
news:df**********@news8.svr.pol.co.uk...

Rather than let a good thread die how about :-

Function IncNum(str As String) As String
'--------------------------------
IncNum = ""

If Len(str) < 1 Then
Exit Function
End If
'--------------------------------
Dim Result As String

Dim re As New RegExp
Dim ms As MatchCollection
Dim m As Match

Dim Digits As String
Dim Incr As Long
Dim i As Long
'--------------------------------
Result = str

re.Pattern = "([0-9]+)"
re.Global = True
re.IgnoreCase = True
Set ms = re.Execute(Result)

Incr = 1
'--------------------------------
For i = ms.Count - 1 To 0 Step -1
Set m = ms(i)

Digits = m.Value
strFormat = String(m.Length, "0")

Digits = Format(Digits + Incr, strFormat)
If Len(Digits) > m.Length Then
Digits = Mid(Digits, 2)
Else
Incr = 0
End If

Mid(Result, m.FirstIndex + 1, m.Length) = Digits

Next 'm
'--------------------------------
IncNum = Result

If Incr > 0 Then
Err.Raise 5, Err.Source, "Unable to increment code string."
End If
'--------------------------------
End Function

Requires reference to ms regular expressions 1.0 or 5.5

Try
?IncNum("000asd999.999.999")

Anyone keeping count of the ways?

Regards John

"qvb jat vke" <qv*******@msn.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl...

"John Nurick" <j.*************@dial.pipex.com>
??????:oa********************************@4ax.com. ..

> On Thu, 1 Sep 2005 15:15:46 -0500, "Malcolm Cook"
> <ma*****@newsgroup.nospam> wrote:
>
> >The function I coded handles your sequence just fine. input "AH-1-G1"

output "AH-1-G2". n'est pas?

>
> The one thing it doesn't seem to handle is leading zeros, e.g. input
> "AB001", output "AB2" (unless you count the trivial case where the
> string contains only digits).
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

[John Griffiths]

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:ey**************@TK2MSFTNGP10.phx.gbl...

As I pointed out elsewhere in this thread, you don't have to have a
reference to MS Regular Expressions to use regular expressions: you can use Late Binding. (Note that you're missing a declaration for strFormat)
Thanks, I refactored out some stuff and removed that at the same time :(
John
Function IncNum(str As String) As String
'--------------------------------
IncNum = ""

If Len(str) < 1 Then
Exit Function
End If
'--------------------------------
Dim Result As String

Dim re As Object
Dim ms As Object
Dim m As Object

Dim Digits As String
Dim Incr As Long
Dim i As Long
Dim strFormat As String
'--------------------------------
Result = str

Set re = CreateObject("VBScript.RegExp")
re.Pattern = "([0-9]+)"
re.Global = True
re.IgnoreCase = True
Set ms = re.Execute(Result)

Incr = 1
'--------------------------------
For i = ms.Count - 1 To 0 Step -1
Set m = ms(i)

Digits = m.Value
strFormat = String(m.Length, "0")

Digits = Format(Digits + Incr, strFormat)
If Len(Digits) > m.Length Then
Digits = Mid(Digits, 2)
Else

'Alternative completion
Mid(Result, m.FirstIndex + 1, m.Length) = Digits
IncNum = Result

Exit Function
End If

Mid(Result, m.FirstIndex + 1, m.Length) = Digits

Next 'm
'--------------------------------
IncNum = Result

If Incr > 0 Then
Err.Raise 5, Err.Source, "Unable to increment code string."
End If
'--------------------------------
End Function
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"John Griffiths" <re***@to.group.only.com> wrote in message
news:df**********@news8.svr.pol.co.uk...

Rather than let a good thread die how about :-

Function IncNum(str As String) As String
'--------------------------------
IncNum = ""

If Len(str) < 1 Then
Exit Function
End If
'--------------------------------
Dim Result As String

Dim re As New RegExp
Dim ms As MatchCollection
Dim m As Match

Dim Digits As String
Dim Incr As Long
Dim i As Long
'--------------------------------
Result = str

re.Pattern = "([0-9]+)"
re.Global = True
re.IgnoreCase = True
Set ms = re.Execute(Result)

Incr = 1
'--------------------------------
For i = ms.Count - 1 To 0 Step -1
Set m = ms(i)

Digits = m.Value
strFormat = String(m.Length, "0")

Digits = Format(Digits + Incr, strFormat)
If Len(Digits) > m.Length Then
Digits = Mid(Digits, 2)
Else
Incr = 0
End If

Mid(Result, m.FirstIndex + 1, m.Length) = Digits

Next 'm
'--------------------------------
IncNum = Result

If Incr > 0 Then
Err.Raise 5, Err.Source, "Unable to increment code string."
End If
'--------------------------------
End Function

Requires reference to ms regular expressions 1.0 or 5.5

Try
?IncNum("000asd999.999.999")

Anyone keeping count of the ways?

Regards John

"qvb jat vke" <qv*******@msn.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl...

"John Nurick" <j.*************@dial.pipex.com>
??????:oa********************************@4ax.com. ..
> On Thu, 1 Sep 2005 15:15:46 -0500, "Malcolm Cook"
> <ma*****@newsgroup.nospam> wrote:
>
> >The function I coded handles your sequence just fine. input "AH-1-G1" output "AH-1-G2". n'est pas?
>
> The one thing it doesn't seem to handle is leading zeros, e.g. input
> "AB001", output "AB2" (unless you count the trivial case where the
> string contains only digits).
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

[John Nurick]

Hi John,

This line
Digits = Format(Digits + Incr, strFormat)
and its implicit conversions offended me, so I tried a bit of tidying
up:

Function IncNum4(str As String, _
Optional ErrorOnFail As Boolean = False) As String
'--------------------------------
Dim Result As String
Dim re As Object 'VBScript.RegExp
Dim ms As Object 'VBScript.MatchCollection
Dim Digits As String
'--------------------------------
Result = str

Set re = CreateObject("VBScript.RegExp")
re.Pattern = "\d+"
re.Global = True

Set ms = re.Execute(str)
If ms.Count > 0 Then 'a match exists
With ms(ms.Count - 1) 'rightmost match
Digits = Right(Format(CLng(.Value) + 1, _
String(.Length, "0")), .Length)
Mid(Result, .FirstIndex + 1, .Length) = Digits
End With
Else
If ErrorOnFail Then
Err.Raise 5, Err.Source, "Unable to increment string."
End If
End If
IncNum4 = Result

End Function

On Mon, 5 Sep 2005 22:00:34 +0100, "John Griffiths"
<re***@to.group.only.com> wrote:

Rather than let a good thread die how about :-

Function IncNum(str As String) As String
'--------------------------------
IncNum = ""

If Len(str) < 1 Then
Exit Function
End If
'--------------------------------
Dim Result As String

Dim re As New RegExp
Dim ms As MatchCollection
Dim m As Match

Dim Digits As String
Dim Incr As Long
Dim i As Long
'--------------------------------
Result = str

re.Pattern = "([0-9]+)"
re.Global = True
re.IgnoreCase = True
Set ms = re.Execute(Result)

Incr = 1
'--------------------------------
For i = ms.Count - 1 To 0 Step -1
Set m = ms(i)

Digits = m.Value
strFormat = String(m.Length, "0")

Digits = Format(Digits + Incr, strFormat)
If Len(Digits) > m.Length Then
Digits = Mid(Digits, 2)
Else
Incr = 0
End If

Mid(Result, m.FirstIndex + 1, m.Length) = Digits

Next 'm
'--------------------------------
IncNum = Result

If Incr > 0 Then
Err.Raise 5, Err.Source, "Unable to increment code string."
End If
'--------------------------------
End Function

Requires reference to ms regular expressions 1.0 or 5.5

Try
?IncNum("000asd999.999.999")

Anyone keeping count of the ways?

Regards John

"qvb jat vke" <qv*******@msn.com> wrote in message
news:ex**************@tk2msftngp13.phx.gbl...

"John Nurick" <j.*************@dial.pipex.com>
??????:oa********************************@4ax.com. ..

> On Thu, 1 Sep 2005 15:15:46 -0500, "Malcolm Cook"
> <ma*****@newsgroup.nospam> wrote:
>
> >The function I coded handles your sequence just fine. input "AH-1-G1"

output "AH-1-G2". n'est pas?

>
> The one thing it doesn't seem to handle is leading zeros, e.g. input
> "AB001", output "AB2" (unless you count the trivial case where the
> string contains only digits).
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.