Increment strings

  • Thread starter Thread starter cley
  • Start date Start date
C

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?
 
cley said:
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?
Click to expand...

You can use a user defined function for this:

Public Function NewKey()
Dim strMax As String
Dim lngNumber As Long

strMax = DMax("thefield", "thetable")
lngNumber = Nz(Val(Mid(strMax, 3), 0) + 1
NewKey = Left(strMax, 2) & Format(lngNumber, "0000")
End Function

Note that most of this fooling around would not be needed if
you had used two fields (one for the string part and another
for the numeric part) or had not even botherd to store the
text part in the table.
 
Back
Top