Algorithm to generate new revision letters automatically

[nosferatu26]

Hello,

I currenlty have a table for all revision letters that get assigned to a given project. In my form, there is a listbox containing all of the projects and the user should have the ability to press a button and add a new revision to it. For example, when a new project is created, it automatically defaults to rev "-". Then if the user wants to create a new rev it would create a new record in their joining table with a rev of "A" instead of "-". The next one would be "B", and so on. This would continue all the way to rev "Z", then, in this case, upon the button getting pressed it would create a new rev of "AA", then "AB", and so on.

I have been having a little trouble with coding this but have been trying to incorporate each ASCII value into it and find a mathematical correlation to these operations.

I have also thought about just prepopulating the rev table with all of these values then just auto incrementing the id but I thought it would be ideal to be able to check if the next rev value exists, then if it does, assign it. otherwise, create it and then assign it to the new record.

Most of it I think is do able. what i want most help on however is the part for when a rev is at "Z" or "AZ", to dynamically get the correct next letter.
I apologize for maybe wording this problem in a confusing matter but if anyone can help me come up with the code for something like this i would be extremly grateful.

 

[jdraw]

Why a letter? What's wrong with version 1,2,3...? easily controlled.

 

[spikepl]

I have been having a little trouble with coding this but have been trying to incorporate each ASCII value into it and find a mathematical correlation to these operations.

"having a little trouble" cuts no ice in a support forum. Read my signature. What did you do, what happened, what do you want to happen? And unless corporate insists on A, AA etc, then I'm with jdraw on numbering.

 

[nosferatu26]

They do insist on letters to represent this. currently, i have the values hardcoded into the rev table and then increment the id if it exists. I dont think doing this should be an issue really cause it will be a static table. I just think it would be better if the table didnt have to be hardcoded prior to adding new revs.

 

[spikepl]

So use the Chr(x) function - it generates each letter according to the ASCII value that you can find on the web. If too much trouble coding this then stick with your predefined table - no one will know the difference.

But - don't muck it up by mixing the tags internally. Internally you have a table

tblRevisionCodes
--------------------
RevCodeID - sequential number (could be autonuymber if you are careful)
RevCode - your letter code


so internally, for each revision, store RevCodeID - this way it is easy to see how far you are, and to retrieve the previous or next revision code.

 

[plog]

My main concern is your table structure. I don't understand why you have a new table set up for every revision if there is a set progression. You only need to store the current revision to know that the others exist. If you have T as your revision and need to go to U, then there's really no reason for storing all those revisions prior, unless you are storing other information with it.

And to pile on to what everyone else has said, you simply need to store numbers. Data storage is not the same as data display. You can still display whatever characters you want. You would simply create a conversion function that implemented your numbering. Something like:

Public Function get_Version(in_Version As Integer) As String
' converst number to characters that represent version, e.g. 1->A, 2->B, etc.

 

[Cronk]

I too prefer using numbers for recording versions. Where I've been asked by a client for lettering, I say it's more complicated ie takes more time and costs the client more. If they insist, I charge a premium for my trouble up front and down the track.

Don't store the letter version code, just the number. It's a simple process to convert if the number of versions is less than 26 * 27 (A-Z, AA-ZZ)

Use mod 26 and chr() to get the first letter (if one) and chr() for the second letter.