Help with Number Field and incrementing

[gwog]

Hi,

We are building a document control tracking database and need some help.

Our document number is in the following format: 2-DEP-000

Where DEP gets replaced by 3 digit department code depending on which department wrote the document. And 000 gets incremented by 1 for each new document from that department.

Such that we could have the following document numbers for example:
2-DQA-001
2-DQA-002
2-DQA-003
2-MET-001
2-MET-002
2-CAL-001
2-CAL-001
2-GEN-001
2-GEN-002

Two things we are strugling with:
1) Making the numeric portion (000) have 3 digits always.
2) How to get the system to tell us the next available number. We are willing to select the department code, but would like it to tell us that the next number available under the selected department code.

Any assistance is greatly appreciated.

 

[BobRodes]

You can use casting to do this. If you cast "003" as an integer, increment it by 1, and cast it back to a string while formatting it to pad with leading zeros, you'll get what you want.

To illustrate this, add a command button and a label to a form. Also add this code:

Option Explicit

Private Sub Command1_Click()
Dim x As Integer
x = CInt(Label1.Caption)
x = x + 1
Label1.Caption = Format(CStr(x), "000")
End Sub

Post back if you need more help.

HTH

Bob

 

[SkipVought]

Hi,

Bob answered the first question.

"2) How to get the system to tell us the next available number."

Do a query on...

NextNbr: Select Max(Right(DocNbr,3))+1
From YourTable
Where DocNbr='[YourDeptSelection]'

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[BobRodes]

<When a wee mystic is on the loose..

Ok Skip,

You may have heard about the fellow that was helping the Undying Dolphins of Ishtar to retain their status by bringing some seagulls to the top of the mountain. When encountering a lion blocking the way, he stepped over it when it refused to move. He was then promptly arrested for "transporting gulls over a staid lion for immortal porpoises."

Bob

 

[gwog]

Thank you both for your assistance! We will give it a try.

 

[SkipVought]

Bob,

Oh yes, and many many more, pun-difications. ;-)

The groanier, the better. Like the Sonic TV Cran-tastic ad - PRICELESS!!!

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[lespaul]

I'm with you Skip, the Cran-tastic ad is great! I don't know who Sonic's marketing manager is, but I love their ads!

It's not punny, but have you seen the Comcast high speed ads with The Slowsky's?

Les

 

[MichaelRed]

The practice of using a "lookk-Up" and increment of a value (wheather string or numeric) has often been addressed herein (Tek-Tips). It is NOT a reliable way to get new values (and unique) values in a multiuser situation!

To assure that you get a unique value, you would need to lock the table for the duration of the find, retrieve and update of the value. Otherwise, another user could retrieve the same "old value" and thus generate a duplicate "new value". I a multiuser environment, this is not pratical.

Please search the faqs for "Automumber". IT IS APPLICABLE!!!



MichaelRed

 

[BobRodes]

Automumber"? I searched, and only found THIS thread.

All kidding aside, Michael has a point, assuming that you will have multiple users attempting to enter new departments at the same time.

 

[gwog]

Good Point MichaelRed we will take that in to consideration.