0

I am in need of a tracking number that automatically generates.

I added this in the 'Before Insert' action (see below), but I am having a problem after the number 9.

Me![Tracking#] = Nz(DMax("[Tracking#]", "[TblTrackingNum]"), 0) + 0.01

My tracking number always starts of with 89669. This code works until it reaches the number ten.

The tracking number should do this: 89669.1... 89669.2... 89669.3... 89669.4... 89669.5.....

But after 9 it changes the number to 89670. I need it to say 89669.10.

Any suggestions?

user2891566
  • 15
  • 2
  • 6
  • 2
    89669.1 + 0.01 = 89669.11 yet you seem to indicate it should be 89669.2 instead. What do you really want? – HansUp Oct 31 '13 at 21:16
  • 1
    I agree with @HansUp that your question in its current form is confusing. It sounds like you might actually want to glue strings of digits together (rather than increment a numeric value with decimal places). If so, then my other answer [here](http://stackoverflow.com/a/19247851/2144390) may be of interest. – Gord Thompson Oct 31 '13 at 21:47
  • You're looking to create a text value, then. If that were a number, 89670.1 and 89670.10 would be the same number. If that was just a typo on your part and you really meant 89670.01, 89670.02, etc..., then you just need to format the number to show 2 decimals. – Johnny Bones Oct 31 '13 at 23:27
  • This tracking number is used to track requests for quotes to our purchasing department. 89669 is the project number so it should always stay the same (that's how we know which project the request is tied to). I just need it to increment by one digit every time we send a request out. There may ultimately be a thousand requests, so in that case it should be 89669.1000. And the next request would be 89669.1001 and so on. I found the string I listed earlier when searching the internet for help….it may be wrong all together. – user2891566 Nov 01 '13 at 12:43

2 Answers2

0

Create a field in a table containing only one row of data:

NextTrackNum Long

initialise NextTrackNum to 1.

When generating your number do this:

function generateNumber() as double
    dim nextnum as long
    nextnum = dlookup("NextTrackNum", "yourTable", filter...)
    generateNumber = CDbl("89669." & trim(nextnum))
end function
John Bingham
  • 1,996
  • 1
  • 12
  • 15
0

First of all, the tracking number needs to be a string, as already mentioned by Johnny Bones in the comments.
So the final field needs to be a string, but the actual increasing number needs to be a numeric type (in order to properly increase it).

Put an auto-increment field into the table (and make it the primary key).
That will become the increasing part of your tracking number.

Then, there are different ways how to generate your tracking number out of that auto-increment field:

1) Create a query in MS Access that selects from the table and generates the tracking number. 

SELECT "89669." & [ID] AS TrackingNumber, YourTable.*
FROM YourTable

Never use the table directly, always SELECT from the query:

SELECT * FROM qryYourTable WHERE ...

2) Create an additional text field called TrackingNumber in the table.
Each time you insert a new row in the table, call a query from your code that fills the field with the correct value:

Public Function FillTrackingNumbers()

    CurrentDb.Execute "UPDATE YourTable SET TrackingNumber = '89669.' & [ID] WHERE TrackingNumber Is Null"

End Function
Christian Specht
  • 35,843
  • 15
  • 128
  • 182