auto increment text

[matt via AccessMonster.com]

Hey guys

I am very much a rookie so any help is much appreciated.

What i have is field that contains text which is representative of a
fixture number. A typical fixture number would be CJ5796-108C

What i want to do is find the biggest number that the last 3 digits would
be so when the add a new record the next number is automatically there.

The problem is i have a table with 1100 fixtures numbers or so. I want the
user to enter a number which would match the first portion of the number
such as CJ5796 then the code should select all fixture numbers with this
number then take the last 3 or 4 digits of the field such as 108C and find
the largest number and add 1. Is this possible?

 

[Lynn Trapp]

Matt,
You probably need to use more than one field -- one for the first portion of
the number, one for the 3 digit portion, and one for the final character (if
the final character is Always a "C", then you wouldn't even need this. Then
you could easily increment the value in the second field using the DMax
function.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[matt via AccessMonster.com]

Hello and thanks for the response. I was hoping that i wouldn't have to
manually go through and change all the fixtures into 3 different fields.
But i am absolutly positive that i have to change them and then concatanate
them to make the sorting work.

Thanks very much for you time

Matt

 

[Lynn Trapp]

You are welcome. good luck with it.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html