Plus One to Alpha Field
- Thread starter phillip
- Start date
- Local time
- Today, 16:50
- Joined
- Feb 28, 2001
- Messages
- 31,032
OK, Phillip - there are answers to this question, but the most important answer is itself a question. WHY do you need to do this?
It APPEARS that you are doing some kind of sequence numbering and that you want to advance to the next number in sequence. The field that you are updating is not inherently amenable to math. The typical method we often advise for doing this kind of manipulation involves a re-evaluation of the computational and relationship needs associated with this field.
The STANDARD way to do this is to first define the requirements of the field when you are NOT incrementing it. Typically, we advise that you break the field into two parts - the letter and the number. If this field is going to be the basis of some kind of relationship to another table, then what you REALLY need is an autonumber field behind the scenes (i.e. never visible to any user) that becomes the primary key. Then when you need to use this table, use a query instead and have the query merge the two fields with a computed field formed by concatenating a text field and a FORMAT function for the numeric field so that you can build the exact tag you want. BUT when referencing the specific record, you use the separate autonumber field for relational linkages.
Note that other strategies than this DO exist. Your problem here is that you designed something that requires pulling it apart to define its successor. I am NOT saying you can't do this. But here are the kinds of questions you need to ask.
1. Can there be another letter than "R" in this field? If so, will you ever need to generate a report grouped on the letter independent of any numbers that may follow it?
2. Will you need to refer to this compound-format field as a primary key? I.e. will the letter-number combination be unique in this table? Can it be changed down the road?
3. Do you have a numeric continuity requirement for this field such that you cannot allow gaps in the numbers for a given letter?
4. What are the limits on the size of the number portion? You show 3 digits. Can it ever be more or less than 3 digits? Can leading zeros be required as part of the numbering sequence?
5. The letter and number appear together in this field. Do they ever appear separately in any other part of the process?
Once you understand the requirements (and the above questions are merely samples of doing that), you can then define how you should approach the problem you just mentioned.
It APPEARS that you are doing some kind of sequence numbering and that you want to advance to the next number in sequence. The field that you are updating is not inherently amenable to math. The typical method we often advise for doing this kind of manipulation involves a re-evaluation of the computational and relationship needs associated with this field.
The STANDARD way to do this is to first define the requirements of the field when you are NOT incrementing it. Typically, we advise that you break the field into two parts - the letter and the number. If this field is going to be the basis of some kind of relationship to another table, then what you REALLY need is an autonumber field behind the scenes (i.e. never visible to any user) that becomes the primary key. Then when you need to use this table, use a query instead and have the query merge the two fields with a computed field formed by concatenating a text field and a FORMAT function for the numeric field so that you can build the exact tag you want. BUT when referencing the specific record, you use the separate autonumber field for relational linkages.
Note that other strategies than this DO exist. Your problem here is that you designed something that requires pulling it apart to define its successor. I am NOT saying you can't do this. But here are the kinds of questions you need to ask.
1. Can there be another letter than "R" in this field? If so, will you ever need to generate a report grouped on the letter independent of any numbers that may follow it?
2. Will you need to refer to this compound-format field as a primary key? I.e. will the letter-number combination be unique in this table? Can it be changed down the road?
3. Do you have a numeric continuity requirement for this field such that you cannot allow gaps in the numbers for a given letter?
4. What are the limits on the size of the number portion? You show 3 digits. Can it ever be more or less than 3 digits? Can leading zeros be required as part of the numbering sequence?
5. The letter and number appear together in this field. Do they ever appear separately in any other part of the process?
Once you understand the requirements (and the above questions are merely samples of doing that), you can then define how you should approach the problem you just mentioned.
"R915" doesn't look like a number to me. Are you using base 28 or more?
In a table, you should store your values with the data type they need to be. If you will be doing math on data then you should store them as numbers. That means the "915" part goes into a field by itself as a number, and the "R" part goes into its own field as text--if that is necessary. If the "R" part is static (you never move to "S"), then you don't need to store it in a table at all, you simple prepend "R" onto that numeric value you are storing.
In a table, you should store your values with the data type they need to be. If you will be doing math on data then you should store them as numbers. That means the "915" part goes into a field by itself as a number, and the "R" part goes into its own field as text--if that is necessary. If the "R" part is static (you never move to "S"), then you don't need to store it in a table at all, you simple prepend "R" onto that numeric value you are storing.
thanks for the suggestion
thanks for the suggestion
GPGeorge
George Hepworth
- Local time
- Today, 14:50
- Joined
- Nov 25, 2004
- Messages
- 3,198
We can tie ourselves into knots trying to finesse a malformed field. This is a good example of that. Several ways to deal with the posted question, but all of them subject to further modification as circumstances, and the data you need to store, change.
The underlying problem is, as Plog explained it and theDocMan expanded on it, the way this field was composed in the first place.
The solution, therefore, is not to craft a clever expression to tease the bits and pieces apart in VBA. The solution is to correct the table design.
The "R" means something and the incrementing digits following it mean something else.
That's two fields, not one.
And, depending on what "R" actually means, it might well need further refinement, or it could just be prepended when needed for DISPLAY purposes for users.
We can't know without getting a detailed explanation of the business rules.
The underlying problem is, as Plog explained it and theDocMan expanded on it, the way this field was composed in the first place.
The solution, therefore, is not to craft a clever expression to tease the bits and pieces apart in VBA. The solution is to correct the table design.
The "R" means something and the incrementing digits following it mean something else.
That's two fields, not one.
And, depending on what "R" actually means, it might well need further refinement, or it could just be prepended when needed for DISPLAY purposes for users.
We can't know without getting a detailed explanation of the business rules.
- Local time
- Today, 17:50
- Joined
- Feb 19, 2002
- Messages
- 48,208
The others have given you multiple conflicting "solutions" on how to add +1 to some part of a number embedded in a string. That will get you past your immediate problem. However, you are up against a different problem pretty soon and Doc started to address that by advising you to separate the letter from the number. The additional problem is that you are at "915". What do you expect to happen when you get to 999 and add +1? Is your number now 000 or 001 or 1000?
Here's a db that shows how to create a pure sequence number and renumber items if necessary as well as a custom unique identifier
Here's a db that shows how to create a pure sequence number and renumber items if necessary as well as a custom unique identifier
Custom Sequence Numbers
Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below
www.access-programmers.co.uk
Gasman
Enthusiastic Amateur
- Local time
- Today, 22:50
- Joined
- Sep 21, 2011
- Messages
- 17,428
I would now go with CJ_London's offering.
Ken Sheridan
Active member
- Local time
- Today, 22:50
- Joined
- Jul 10, 2025
- Messages
- 158
Do all rows have the leading character R in this column, or do subsets of rows have different leading characters?
If the former then there is no need to store the leading character in each row, as you can concatenate it as a constant to the numeric characters whenever necessary. This begs the question whether the numeric values can have leading zeros or not. If they do, then you have two options: you can either store the value as an integer number data type and, as well as concatenating the R to it when necessary, format the numeric element as "000", or you can store the number, including any leading zeros, as a text data type.
If subsets of rows can have different leading characters, rather than the constant R, do the numeric elements for each prefix letter represent independent sequences? If so, then computing the next number would be done along the lines of the following function, which numbers rows independently for each gender:
Code:
Private Function GetNameID(strGender As String, intStartSequence As Integer)
strCriteria = "Gender = """ & strGender & """"
GetNameID = Nz(DMax("NameID", "NamesByGender", strCriteria), intStartSequence - 1) + 1
End FunctionThis function is taken from the attached little demo file, which illustrates a number of methods for generating sequences, and includes error handling to cater for two or more users inserting a new row simultaneously in a multi-user environment. It also allows for the next number to be seeded rather than simply incrementing the highest current number by 1.
