Checking text field for highest numeric value used

[ncsthbell]

I have a column that is defined as a text field called 'Trip number'. The value may be 'ABC' , '1', '33', whatever the user assigns to it. Now they want me to 'autofill' the TripNumber with the next numeric value and they will autofill if they choose to. I don't see a function that I can use for this. Does anyone have any ideas?

[damonreid]

Two quick questions,
1) Does the number increase regardless of the initial letters?
2) Can you change the format of the trip number so there are an equal number of numeric characters for each entry?

[abolos]

I have a column that is defined as a text field called 'Trip number'. The value may be 'ABC' , '1', '33', whatever the user assigns to it. Now they want me to 'autofill' the TripNumber with the next numeric value and they will autofill if they choose to. I don't see a function that I can use for this. Does anyone have any ideas?

Use this:

Select MAX(field) from <tablename>

This sql will bring you the max number in the table, then add to it 1. Use the Insert or Update sql statement to update it if they want.

For any other help feedback,
Abolos

[ncsthbell]

Two quick questions,
1) Does the number increase regardless of the initial letters?
2) Can you change the format of the trip number so there are an equal number of numeric characters for each entry?

The number is not consecutive. Crazy but this is what my users want. The may have used ABC, 5, 3, 100, XX as example. They would want me to use 101 as the next trip nbr.

Not sure if I can change the format for the number. For example, do you mean to change it to 5 character so the number would have to be entered like '00005'? What about the alpha characters they use. They have some 'ABC', if I put 5 digits in the format, how would it be handled for alphanumeric?