Results 1 to 5 of 5
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Incremental count

    I am attempting to do an incremental count that is with a loop, within a loop. I have 6 types of Sets. With the following alphanumeric 5 digit numbering system. I am trying to find out how to do the fifth digit incrementally by Sets.

    WN = First character '0-9 & A-F


    Z = Second Character '0-9 & A-F
    Y = Third Character '0-9 & A-F
    X = Fourth Character '0-9 & A-F
    Q would be the Fifth Character '0-........

    Code:
    Do While Not rU.EOF
            If ......... Then
                WN = 1
            ElseIf .....Then
                WN = 2
            Else: WN = 0
            End If
     
              Uname = BName
                Select Case BName
                    Case Else
                        Z = 0
                        Base = ""
                End Select
     
            CName = b
                Select Case CName
                    Case Else
                        b = ""
                        PN = ""
                End Select
     
         PName = rU![PL]
                Select Case PName
                    Case Else
                        a = ""
                        X = 0
                End Select
     
    For I = 1 To 2
        If ........... Then
                    Code for Set1  ' WN  = 1    Q starts at 1
        End If
     
        If ........... Then
                    Code for Set2  'WN  =  6    Q starts at 1
        End If
     
        For M = 0 To 9
        If ........... Then
                    Code for Set3   'WN = 2    Q starts at 1
            For K = 1 To 4
                    Code for Set3   'WN = 2    Q starts at 1
                End If
            Next K
        Next M
    Next I
     
    For I = 1 To 6
          If ........... Then
                    Code for Set4  'WN = 5    Q starts at 1
        End If
    Next I
     
        If ........... Then
                    Code for Set5  'WN = 3    Q starts at 1
        End If
        If ........... Then
                    Code for Set6  'WN = 4    Q starts at 1
        End If
    Loop

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I don't get it! Can you give an example of the starting value (are you starting from nothing?) and what the end result will look like.

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Apologies for the delay in answering. I believe I have all the nets but the below referenced:

    Code:
    For I = 1 To 6  
        If rU("SINCG  " & I) = "METT" Then
                  rU("SINC" & I) = "5F000-METT-T" & V
        ElseIf rU("SINCG  " & I) Like "Div*" Then
                  rU("SINC" & I) = "5800" & I + 1 & "-" & DIV & " " & Right(rU("SINCG  " & I), 3) & V
        ElseIf rU("SINCG  " & I) Like "Bde*" Then
                  rU("SINC" & I) = BDEs & " " & rU("SINCG  " & I) & V
        ElseIf rU("SINCG  " & I) = "MED" Then
                  rU("SINC" & I) = "MED" & V
        ElseIf rU("SINCG  " & I) Like "INF BN #*" Then
                  NewWave = Right(rU("SINCG  " & I), (Len(rU("SINCG  " & I)) - 9))
                  rU("SINC" & I) = Base & " " & NewWave & V
        ElseIf rU("SINCG  " & I) Like "CAV*" Or rU("SINCG  " & I) Like "BEB*" Or rU("SINCG  " & I) Like "BSB*" Then
                   NewWave = Right(rU("SINCG  " & I), (Len(rU("SINCG  " & I)) - 4))
                   rU("SINC" & I) = Base & " " & NewWave & V
        ElseIf rU("SINCG  " & I) = "FA Bn*" Then
                  NewWave = Right(rU("SINCG  " & I), (Len(rU("SINCG  " & I)) - 3))
                  rU("SINC" & I) = Base & " " & NewWave & V
        ElseIf rU("SINCG  " & I) Like "FIRES ?*" And Not rU("SINCG  " & I) Like "FIRES Bn M*" And Not rU("SINCG  " & I) Like "*TGT Plt Ops" Then
                  NewWave = Right(rU("SINCG  " & I), (Len(rU("SINCG  " & I)) - 6))
                  rU("SINC" & I) = Base & " " & NewWave & V
        ElseIf rU("SINCG  " & I) Like "FIRES Bn M*" Then
                  NewWave = Right(rU("SINCG  " & I), (Len(rU("SINCG  " & I)) - 6))
                  rU("SINC" & I) = Base & " " & NewWave & V
        ElseIf rU("SINCG  " & I) Like "*TGT Plt Ops" Then
                  rU("SINC" & I) = Base & " TGT ACQ Plt Ops" & V
        ElseIf rU("SINCG  " & I) Like "BEB BN*" Then
                  NewWave = Right(rU("SINCG  " & I), (Len(rU("SINCG  " & I)) - 6))
                  rU("SINC" & I) = Units & " " & NewWave & V
        ElseIf rU("SINCG  " & I) Like "BSB*" Then
                  NewWave = Right(rU("SINCG  " & I), (Len(rU("SINCG  " & I)) - 6))
                  rU("SINC" & I) = Base & " " & NewWave & V
        ElseIf rU("SINCG  " & I) = "Sptd Unit A&L" Then
                  rU("SINC" & I) = Units & " AL" & V
        ElseIf rU("SINCG  " & I) <> "" Then
                  rU("SINC" & I) = Base & " " & rU("SINCG  " & I) & V
        Else
                  rU("SINC" & I) = Base & " " & rU("SINCG  " & I) & V
    
        End If
    Next I
    rU("SINC" & I) = Base & " " & NewWave & V would be replaced by another set:

    Net = WN & Z & Y & X

    rU("SINC" & I) = Net & Q & "-" Netbase & rU("SINCG " & I) & V where Q is the count of:


    Q=1
    IF Net is the same then
    Q=Q+0
    if Net <> then
    Q=Q+1
    or somesuch. However it needs to keep track to see if the net has been used before, if so then it needs to use that Net + Q.

    I am not sure if theat clarifieis anything.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I have to admit defeat on this, I'm afraid. It is a really complicated numbering system!

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Thompyt View Post
    I am attempting to do an incremental count that is with a loop, within a loop. I have 6 types of Sets. With the following alphanumeric 5 digit numbering system. I am trying to find out how to do the fifth digit incrementally by Sets.

    WN = First character '0-9 & A-F
    Z = Second Character '0-9 & A-F
    Y = Third Character '0-9 & A-F
    X = Fourth Character '0-9 & A-F
    Q would be the Fifth Character '0-........
    I have been following this thread and I still don't understand what you are trying to do.
    Can you give an example of what you want? What does a "set" look like?


    I looked at your code last night in Post#3 and I have some concerns:
    Code:
    For I = 1 To 6       
        If rU("SINCG  " & I) = "METT" Then
                  rU("SINC" & I) = "5F000-METT-T" & V
        ElseIf rU("SINCG  " & I) Like "Div*" Then
                  rU("SINC" & I) = "5800" & I + 1 & "-" & DIV & " " & Right(rU("SINCG  " & I), 3) & V
        <snip>
    Next
    I see a few things that concern me:
    It appears that you have six fields named "SINCG 1", "SINCG 2", ...., "SINCG 6" and six fields "SINC1", "SINC2", ... , "SINC6".
    This violates the rules of Normalization because they are repeating fields. (if these 12 fields are in the same record)
    The fields that begin with "SINCG" appear to have 2 spaces in the name. ( looking at the code - the forum appears to be deleting one space in the name in this post)



    I am going to assume that you are using DAO to open the recordset.
    Code:
        If rU("SINCG  " & I) = "METT" Then
                  rU("SINC" & I) = "5F000-METT-T" & V
    A field in a record set is NOT the same as a variable in code!!
    You cannot just use
    Code:
    rU("SINC" & I) = "Hi"
    to change the value of a field in a record set without using the EDIT command and UPDATE command.
    Example:
    Code:
    rU.Edit
    rU("SINC" & I) = "Hi"
    rU.Update

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Incremental field
    By gwboolean in forum Access
    Replies: 3
    Last Post: 09-17-2016, 10:14 PM
  2. Incremental Number in a Query
    By jmauldin01 in forum Access
    Replies: 5
    Last Post: 01-01-2014, 01:23 AM
  3. Incremental Numbers
    By dr4ke in forum Queries
    Replies: 7
    Last Post: 01-16-2013, 03:46 AM
  4. Incremental Table Updating
    By pyrman in forum Queries
    Replies: 4
    Last Post: 07-23-2012, 11:35 AM
  5. Autonumbers and Incremental Numbers
    By stacies in forum Access
    Replies: 2
    Last Post: 01-27-2012, 02:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums