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?

 

[Marshall Barton]

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?

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.