Increment strings

[cley]

I have a field which contains records in the
format "SN0001", "SN0002", "SN0003", etc. For new records,
how can I automatically determine the value of this field
for a new record such that the numerical part of the field
gets incremented by 1 without omitting the zeroes?

 

[Dan Artuso]

Hi,
Here is a function that will return the incremented value.
Pass it the old value and it returns the new value incremented by one.
But, you must stick to the format you've supplied. Once you reach
SN9999, it will start over at SN0000. If you want to test it out,
open any code module, press Ctl-G (to bring up the Debug window).
Type in:
?IncrementSN("SN0001")

hit return and you should see:
SN0002

Public Function IncrementSN(sn As String) As String
Dim strTemp As String
Dim lngSn As Long

strTemp = Mid(sn, 3)
Do While Left(strTemp, 1) = "0"
strTemp = Mid(strTemp, 2)
Loop

lngSn = CLng(strTemp)
lngSn = lngSn + 1
strTemp = CStr(lngSn)
Select Case Len(strTemp)
Case 1
IncrementSN = "SN000" & strTemp
Case 2
IncrementSN = "SN00" & strTemp
Case 3
IncrementSN = "SN0" & strTemp
Case 4
IncrementSN = "SN" & strTemp
Case 5
IncrementSN = "SN0000"
End Select
End Function