Re: Next Issue?!

From: Graham R Seach (gseach_at_NOSPAMpacificdb.com.au)
Date: 06/21/04 

Date: Mon, 21 Jun 2004 10:51:19 +1000

Daniel,

Is this what you're after?

Public Function NextDwgNo(sDwgNo As String, _
    Optional IsLastDwg As Boolean = False) As String
'Create an alphanumerically incremented drawing number for the
'specified drawing.
    'sDwgNo must be in the format: xxnn
    ' where xx = any number of alpha characters
    ' nn = any number of numeric digits
    '
    Dim sTmp As String
    Dim sCharPart As String
    Dim sNumPart As String
    Dim iCtr As Integer

ExtractNos:
    'Extract the alpha and numeric portions
    For iCtr = 1 To Len(sDwgNo)
        If IsNumeric(Mid(sDwgNo, iCtr, 1)) Then
            sNumPart = sNumPart & Mid(sDwgNo, iCtr, 1)
        Else
            sCharPart = sCharPart & Mid(sDwgNo, iCtr, 1)
        End If
    Next iCtr

    'Work out what to increment
    For iCtr = Len(sCharPart) To 1 Step -1
        If UCase(Mid(sCharPart, iCtr, 1)) <> "Z" Then
            'Increment the character
            sCharPart = Left(sCharPart, iCtr - 1) & _
                        Chr(Asc(Mid(sCharPart, iCtr, 1)) + 1) & _
                        String(Len(sCharPart) - iCtr, "A")
            Exit For
        Else
            If iCtr = 1 Then
                sCharPart = "A" & String(Len(sCharPart), "A")
            End If
        End If
    Next iCtr

    'Format the new DwgNo
    NextDwgNo = sCharPart & String(Len(sNumPart), "0")
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

"Daniel" <Daniel@discussions.microsoft.com> wrote in message
news:45B92E77-0969-41C5-8E83-A2B7D41BA4CB@microsoft.com...
> Hello,
>
> How can I generate the next drawing issue based on an existing issue?
> It should always be the next letter in the alphabet
>
> for instance:
> I have a table "Drawing History Tbl" which has three columns (ID, Drawing
Number, Drawing Issue). I would like when the user click the command button
that it would create a new entry in that table at the next logical issue.
>
> thus if the greatest issue for that specific drawing number was B00 it
would create an entry for C00, if the greatest issue for that specific
drawing number was B03 it would create an entry for C00,if the greatest
issue for that specific drawing number was AH00 it would create an entry for
AI00 and so on ...
>
> Thank you
>
> Daniel