ACC: How to Increment the Numeric Portion of a String

ID: Q88169

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

This article describes a formula that you can use to set the default value of a bound Text field in a form to the next higher numeric value (default value = default value + 1).

The sample formula does the following:

It finds the highest numeric value used in a table.

It strips any leading text characters.

It adds 1 to the value found in step 1.

It reconnects the leading text characters.

NOTE: This example assumes that the number of leading text characters is known at the time that the form is designed.

MORE INFORMATION

Use the following sample procedure to increase the default value of a bound Text field on a form:

Open an existing database or create a new database.

Create a new table in Design view.

Add a field called Book ID with a Text data type. On the Edit menu, click Primary Key.

NOTE: In versions 1.x and 2.0, click Set Primary Key on the Edit Menu.

Add a second field of any data type.

Save the table as Increment.

Switch to Datasheet view and enter the following records:


       Book ID       Second Field
       --------------------------
       BO-110
       BO-111
       BO-112

Close the table.

Create a new form in Design view based on the Increment table.

If it is not displayed, click Field List on the View menu.

Drag the Book ID field and the second field from the field list to the form.

Verify that the form's DefaultView property is set to Single Form.

Select the Book ID control.

Set the DefaultValue property of the Book ID text box to the following code:

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this expression.
```
       ="BO-" & Right(DMax("[Book ID]","Increment"),Len(DMax_
          ("[Book ID]","Increment"))-3)+1 
```

View the form in Form view and enter a new record, the Book ID field increments to the next available number automatically.

The components of the formula for the DefaultView property are as follows:

DMax("[Book ID]","Increment") locates the highest value in the table.

Len(DMax(...)) finds the length of the value returned by the DMax() function.

Right(DMax(...),Len(...)-3) strips the three leading text characters.

+1 increments the result of the Right() function.

"BO-" concatenates the text "BO-" to the incremented number.

Note that this example works correctly when the form's DefaultView property is Single View; it may not work correctly in Continuous View. When you move to a new record and begin to enter data, Microsoft Access displays the next empty record. The default values for this record are calculated before the record that you are currently editing is committed. Because the DMax() function calculates the maximum value from those records stored in the table, the Book ID of the record you are currently editing is repeated.

If you are working in a multiuser environment, it is possible that more than one user may receive the same calculated Book ID value. Although you can manually change the Book ID, you can also maintain the highest numeric value in a separate table, using a macro or Visual Basic code (or Access Basic in versions 1.x and 2.0).

Additional query words: custom counter


Keywords          : kbusage TblPriky 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: March 10, 1999