Incrementing Text Field
Posted on 2003-11-13
Okay, I am on the search for the answer to this question. I know I can always count on you people to help me with the solution, while I am also searching through my "magical" books for the answer. Here goes:
I have a primary key that is data type of text, 6 field size. I need it to increment 1 value at a time.
AL001...keeping the place holder of 001, next one being AL002 and so on. It will never go over 999. But I need the 00# placeholders to keep it 6 characters always.
Now here is the "down and dirty part"...it goes by state. So AL starts with 001 and keeps going for all Alabama clients. Then CA starts with 001 and keeps going for all California clients, and so forth.
I was thinking of making the state and then a text field of 3 field size and increment it, both being the primary key, but then how does it know to start over when the state field is different, ya know what I mean?
Does anyone have any ideas on how to accomplish this task? I would greatly appreciate it!