How Do I Increment Strings i.e A - B - C???

[Shokoya]

Hi Guys,

I am using the following code to increment numbers;

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT [Sequence_No] FROM Quote_Number&quot
rst.MoveFirst
With rst 'Edit recordset object
.Edit 'Edit the field
![Sequence_No] = ![Sequence_No] + 1 'Increment by one
seq = ![Sequence_No]
.Update 'Save changes
End With

Does anyone know how I might adapt this to be able to increment letters to take the current letter and the replace it with the next letter in the alphabet i.e a becomes b, b becomes c, etc???

[Cheers]

 

[KenReay]

Hi

Inceremented = Chr(asc(Original)+1)

where Incremented is the 'new' value (say B) and Original is the 'old' value (say A)

so

Dim strLetter as String
Dim i as Integer
strLetter = "A"
For i = 0 to 26
strLetter = chr(asc(strLetter)+1)
debug.print strLetter ' will give A, B, C and so on
Next i


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[rt52]

And to add to the above,
Chr(97) = "a", Chr(98) = "b" , etc.
(just add 32 to upper case to get lower case)
rmt

 

[Shokoya]

Thanks guys....one slight prob though...each time i run the code i only seem to be getting |symbol???

Any one know why???

again

 

[MichaelRed]

No clue from here (MD), but KenReay's Loop example will go a bit beyond the Upper case alphabet (the loop index should stop at 24, to limit the charactes specifically to the alphabet.

In general, the soloutions shown share the 'fault' (or limitation) of being simplistic examples of the functionallity, and do not account for (or consider) the possablity (guarntee) that the usage -other than the example- that the function would return all characters from the initial ("seed&quot character through the end of the ASCII set [Chr(255)], and then return an error (invalid procedure call).

In particular, the process discription you provided also leaves out any mention of how you want the function to 'continue' beyond the example series. Shoule it wrap back to "A" after "Z", lengthen the string (alphabetically from "Z", go to "AA&quot or other 'legal' progressions.

Without intending to slight previous posts (or posters), the functionallity shown in this thread can easily be found using the ubiquitous {F1} a.k.a. Help

Finally, this has been discussed in detail in other threads in these (Tek-Tips) fora, and examples presented for many of the more common (logical?) progressions. Use Advanced Search (in the Header area of each page) with "basIncrStr" as the Keyword to find at least a couple of instances.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[KenReay]

Hi

Oops, yes Michael fancy not knowing how many letters there are in the alphabet!

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[rkasnick]

The problem with Ken Reay's solution is that a) it does not start at A but since strLetter is initialized to A the output starts with B, to start output at A the strLetter should be initialized with the ASC character BEFORE the A, in this case the @ sign and b) it does not stop at Z but gives the ASC character AFTER the Z ( the [ ) because his loop runs 27 times (0 to 26) so to limit the output to Upper case only change the loop bounds to 1 to 26. Now you will have correct output.

Another solution is to print the output BEFORE incrementing, that will force A to print then increment to B, but you will still have to change the bounds of the loop to either 0 to 25 or 1 to 26


Regards
Rod

 

[psemianonymous]

First, KenReay's answer perfectly matched the question asked, so his answer is technically right, though incomplete.

As Michael might have pointed out, the question is something that has been asked before. Maybe the reason why you didn't notice the previous threads is that you may not be doing what we think you're doing. Why are you trying to increment the quotenumber? Are you autogenerating a primary key?

If so, then this has definitely been covered before, and you shouldn't go about it in any way like your original code. There are functions, so look them up using this forum's search.

If not, then you need to explain why you would want to update (only) the first record in a recordset's field to the next character in the alphabet. The code itself doesn't really make sense.


Pete

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm