# Thread: Sequential Number with anothe field

1. Registered User
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. Registered User
Join Date
May 2005
Location
Posts
2,888
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

3. Registered User
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 10:08.

4. Registered User
Join Date
May 2005
Location
Posts
2,888
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.

5. Registered User
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. Registered User
Join Date
May 2005
Location
Posts
2,888
What have you got, and where? It pulls the right number but doesn't save? What does "dissapears from the field" mean exactly?

7. Registered User
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. Registered User
Join Date
May 2005
Location
Posts
2,888
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?

9. Registered User
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. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
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```

11. Registered User
Join Date
May 2005
Location