VBA, Automatically Assign Sequential Letter Values

Automatically Assign Sequential Letter Values

Author	Message
RE #1 / 3	Automatically Assign Sequential Letter Values I have created a procedure that will allow users to automatically assign codes to a sequenced list of items. They select a start letter and a gap for the sequencing numbers. For example, Letter: K, Gap: 5 will assign K005, K010, etc. through K995. However, when there are more than 199 records (in the case of a gap of 5), I need for the letter to change, so the next value after K995 will be L000. I haven't been able to accomplish this part of the procedure. The start letter and gap are assigned by the user through a simple form, and I use the following code to assign the values: Do Until rstPLISN.EOF With rstPLISN .Edit !PLISN = Forms!PLISNFrm.Letter & IIf((Forms!PLISNFrm.Gap * (rstPLISN.AbsolutePosition + 1)) Like "#", "00" & (Forms!PLISNFrm.Gap * (rstPLISN.AbsolutePosition + 1)), _ IIf((Forms!PLISNFrm.Gap * (rstPLISN.AbsolutePosition + 1)) Like "##", "0" & (Forms!PLISNFrm.Gap * (rstPLISN.AbsolutePosition + 1)), (Forms!PLISNFrm.Gap * (rstPLISN.AbsolutePosition + 1)))) .UPDATE .MoveNext End With Loop Any suggestions on automatically advancing to the next letter would be greatly appreciated. Thanks, ..Bob
Thu, 18 Jan 2001 03:00:00 GMT

Mike S #2 / 3	Automatically Assign Sequential Letter Values Check the asc code and add one. e.g. If your check found CurrID= K995 then nextLetter = Chr(Asc(Left(CurrID, 1)) + 1) Make to sure to check for upper and lower case and not to exceed the alphabets' ASCII code. Look in help for ASCII for valid codes. -- Mike S. Optimal Systems Corp. Access Developer Tools www.oscorp.com -- Quote: >I have created a procedure that will allow users to automatically >assign codes to a sequenced list of items. They select a start letter >and a gap for the sequencing numbers. For example, Letter: K, Gap: 5 >will assign K005, K010, etc. through K995. >However, when there are more than 199 records (in the case of a gap of >5), I need for the letter to change, so the next value after K995 will >be L000. I haven't been able to accomplish this part of the >procedure. >The start letter and gap are assigned by the user through a simple >form, and I use the following code to assign the values: >Do Until rstPLISN.EOF > With rstPLISN > .Edit > !PLISN = Forms!PLISNFrm.Letter & IIf((Forms!PLISNFrm.Gap * >(rstPLISN.AbsolutePosition + 1)) Like "#", "00" & (Forms!PLISNFrm.Gap >* (rstPLISN.AbsolutePosition + 1)), _ > IIf((Forms!PLISNFrm.Gap * (rstPLISN.AbsolutePosition + 1)) Like >"##", "0" & (Forms!PLISNFrm.Gap * (rstPLISN.AbsolutePosition + 1)), >(Forms!PLISNFrm.Gap * (rstPLISN.AbsolutePosition + 1)))) > .UPDATE > .MoveNext > End With >Loop >Any suggestions on automatically advancing to the next letter would be >greatly appreciated. >Thanks, >..Bob
Thu, 18 Jan 2001 03:00:00 GMT

Adrian Edward #3 / 3	Automatically Assign Sequential Letter Values A quick answer would be Sub trial() Dim TheInput As String Dim letters As String Dim inc As Integer Dim TheOutput As String Dim numbers TheInput = "K995" letters = Mid(TheInput, 1, 1) ' ie K numbers = Eval(Mid(TheInput, 2, 4)) ' ie 995 inc = Int((numbers + 5) / 1000) ' Are we going over 999 if so inc is 1 ' otherwise its zero numbers = (numbers + 5) Mod 1000 ' strip the part less than 1000 out. letters = Chr(Asc(letters) + inc) ' Add either 1 or zero to the the letter TheOutput = letters & Format(numbers, "000") MsgBox "The output is " & TheOutput, vbInformation End Sub Adrian Quote: >Check the asc code and add one. e.g. >If your check found CurrID= K995 then > nextLetter = Chr(Asc(Left(CurrID, 1)) + 1) >Make to sure to check for upper and lower case and not to exceed the >alphabets' ASCII code. Look in help for ASCII for valid codes. >-- >Mike S. >Optimal Systems Corp. >Access Developer Tools www.oscorp.com >-- >>I have created a procedure that will allow users to automatically >>assign codes to a sequenced list of items. They select a start letter >>and a gap for the sequencing numbers. For example, Letter: K, Gap: 5 >>will assign K005, K010, etc. through K995. >>However, when there are more than 199 records (in the case of a gap of >>5), I need for the letter to change, so the next value after K995 will >>be L000. I haven't been able to accomplish this part of the >>procedure. >>The start letter and gap are assigned by the user through a simple >>form, and I use the following code to assign the values: >>Do Until rstPLISN.EOF >> With rstPLISN >> .Edit >> !PLISN = Forms!PLISNFrm.Letter & IIf((Forms!PLISNFrm.Gap * >>(rstPLISN.AbsolutePosition + 1)) Like "#", "00" & (Forms!PLISNFrm.Gap >>* (rstPLISN.AbsolutePosition + 1)), _ >> IIf((Forms!PLISNFrm.Gap * (rstPLISN.AbsolutePosition + 1)) Like >>"##", "0" & (Forms!PLISNFrm.Gap * (rstPLISN.AbsolutePosition + 1)), >>(Forms!PLISNFrm.Gap * (rstPLISN.AbsolutePosition + 1)))) >> .UPDATE >> .MoveNext >> End With >>Loop >>Any suggestions on automatically advancing to the next letter would be >>greatly appreciated. >>Thanks, >>..Bob
Thu, 18 Jan 2001 03:00:00 GMT

Page 1 of 1

[ 3 post ]