is there a way to incrementally increase a number that's part of a name

Question

I have a table of server names (tblServerNames).  I have a query that filters that list based on things like OS, City, app and physical/virtual (W CHI DB V).  This query result filters the list in tblServer Names and populates a text field.  The server names then have 3 numbers at the end (WCHIDBV001).  This works fine but we have to look at the query results, go to the end of the list and then manually type in 002 at the end of the text field and click save, which writes the field to tblServerNames.  Is there a way to have Access show the last server in the list and add 001 to the number?  And of course when it gets to 009 go to 010 (and same for 99 to 100)?

Answer 1

Hi,

You might be able to use a combination of the Right() and Val() functions to get what you want. For example, Right([ServerName],3) will return the last three characters of the server's name. Then, the Val() function can convert it into a number, so you can get the highest value and add 1 to it.

Hope it helps...

Answer 2

Don't forget it will need to be padded with zeros so you end up with 3 digits.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 3

Hi Carl_S_S,

I can see that after posting the issue , you did not follow up this thread.

is your issue solved?

if yes, I suggest you to post your solution and mark it as an answer.

if the issue is still exist then refer the suggestion given by the community members.

if you have any further questions regarding this issue then let us know about that.

we will try to provide further suggestions.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].