Home  |  About  |   Search   

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

RunCommand Constants

Terms of Use


 

Strings: Increment Numeric portion of a string

Author(s)
Dev Ashish

(Q)    I have a field which contains records in the format "REC-1", "REC-2", "REC-3" 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.

(A)    Use the Right and DMax function to return the numeric part of the highest value of the field "FOO" and add one to it, concatenating the result in the end to "REC-". You can either use the following example as the DefaultValue of the field or assign it manually on the form using AfterUpdate event of another control. For example, if the field name in the above example is "FOO" and the tableName FOOTable, then the expression would be

="REC-" & right(DMax("FOO", "FOOTable"), _
    Len(DMax("FOO", "FOOTable")) - _
    InStr(1, DMax("FOO", "FOOTable"), "-")) + 1

Note: As the multiple calls to DMax function can slow down this operation on a large table, I'd suggest against using such an expression as DefaultValue. Instead, assign this new value to a hidden control on your form which is bound to field FOO. This way you only have to use DMax once. For example,

Private Sub SomeField_AfterUpdate()
Dim strMax as string
	strMax =DMax("FOO", "FOOTable")
	me!HiddenFooCtl = "REC-" & right(strMax, _
			len(strMax) - _
			Instr(1,strMax, "-")) +1
End Sub

1998-2009, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer