Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2009
    Posts
    120

    Unanswered: Sequential Number with anothe field

    Hello Everyone,

    Appologies if this question has been asked before.

    I have a table that has a field called Charge Category. It contains a 3 digit number. There are various different numbers included in this field that range from 111 to 999

    I want to use that 3 digit number and then add 5 additonal digits to it and increase each by 1.

    So I would end up with:

    111000001
    111000002
    111000003 etc.

    Is it possible to do this with some sort of formula or something?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    See the DMax near the bottom, using your field instead of the year field. Just store a number like 1, 2 etc. Use the Format() function to pad the zeros.

    http://www.baldyweb.com/CustomAutonumber.htm
    Paul

  3. #3
    Join Date
    Mar 2009
    Posts
    120
    Thank you. I'll give it a shot.

    One additional question though. This method will number sequntially for all my already existing numbers.

    So using my example above I would get the following:

    111000001
    111000002
    111000003

    And then when a different three digit is used it would be:

    121000004
    121000005 etc

    I would want the 121 to start with 1 as well. So it would be:

    121000001
    121000002 etc

    In the link provided, it mentioned a table for the first available number, but only using a single value, would that work if I were to list the my 3 digit codes and then link it to my query?

    Also I think i am going to need some assistance in writing this out. I've tried to do it but I am not doing something right, it keeps dissapearing from the query.

    My Tables are called: CDM_20120308 which contains the information I want to use, the field is "Chg Cat", I did create another table called "Start Numbers" which has 2 fields in it: "ChgCat" and "NumberField". Chg Cat contains the 3 digit number that would be in the other table and NumberField contains a 1 as the starting point for each.
    Last edited by CHI Brian; 03-09-12 at 11:08.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No, it would start at 1 for the new 3 digit code. The method using DMax doesn't require another table. It simply looks up the highest existing value for the given 3 digit code and adds 1 to it.
    Paul

  5. #5
    Join Date
    Mar 2009
    Posts
    120
    Okay Thanks.

    I am still not sure exactly how to write the expression though. I've tried but like I said before it dissapears from the field.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What have you got, and where? It pulls the right number but doesn't save? What does "dissapears from the field" mean exactly?
    Paul

  7. #7
    Join Date
    Mar 2009
    Posts
    120
    When I am typing the Dmax into a query field when i finish and Save the Builder window the formula I just typed dissapears, it doesn't stay in the field I put it into.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You generally wouldn't use that in a query. You'd have it somewhere in the form where the user is entering new records, to assign the new number. If you just want to display a number in a query, you'd use DCount() or a subquery. What exactly are you trying to accomplish?
    Paul

  9. #9
    Join Date
    Mar 2009
    Posts
    120
    Okay.
    I have a list of items from our system. They currently have uniquie number assigned to them.

    However, we are going through a new system install and I am attempting to show what these same items would look like with different types of numbering schemes.

    So I am trying to number them using the 3 digit number I've refered to here as well as a 4 digit department number.

    I have a table of how they look now which is a download and import from our existing system.

    I just need to give them the New numbers based on the two examples above.

  10. #10
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Here is AC 2000 code that can be used as a model to do this conversion. The 3-digit codes are initially put in a temporary table
    to ensure that they are in the correct order (ascending), and a field is added to hold the new code. You can run this from the
    module in the attached file.

    Code:
    Function Make_Codes()
    Dim rst As Recordset
    Dim recCount As Long, index As Long
    Dim seqNo As Integer
    Dim saveValue As String, tempText As String
    Dim sqlText As String
    
        sqlText = "SELECT Table1.Code1, 0 AS Code2 INTO TempTbl "
        sqlText = sqlText & "FROM Table1 "
        sqlText = sqlText & "ORDER BY Table1.Code1;"
    
        DoCmd.SetWarnings False
        DoCmd****nSQL sqlText
        DoCmd.SetWarnings True
    
        Set rst = CurrentDb.OpenRecordset("TempTbl", dbOpenTable)
        rst.MoveFirst
        saveValue = rst!Code1
        recCount = rst.RecordCount
        
        For index = 1 To recCount
            seqNo = 1
            Do While rst!Code1 = saveValue
                tempText = "00000" & CStr(seqNo)
                rst.Edit
                rst!Code2 = rst!Code1 & Right(tempText, 6)
                rst.Update
                seqNo = seqNo + 1
                rst.MoveNext
                If rst.EOF Then Exit For
            Loop
            saveValue = rst!Code1
        Next
    
        Set rst = Nothing
        
    End Function
    Attached Files Attached Files

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Post back if Jerry's method doesn't work for you. I would think it could be done with an UPDATE query, as long as there was a sort order that could be imposed for a DCount.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •