Solved Sequential number, display as 001 rather than 1 (1 Viewer)

[Manicduck]

Hi everyone,

sorry to be a pain again, but I seem to be running around in circles......

I need a sequential number in my DB which should display in the report as a 3 digit number, i.e. 001.

Searching this site has given me the code which will increase the number each time a form is submitted and this works well, but the number is displayed as a single digit.

I have messed with formats and masks but my knowledge level is sufficient to be dangerous but not enough to be effective
Ideally I would like a 3 digit number in the table, but I would settle on it just being displayed on the report if it is not possible on the table

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.[Report_ID]) Then
       Me.[Report_ID] = Nz(DMax("[Report_ID]", "tbl_Report"), 0) + 1 
    End If
End Sub

thank you

 

[jdraw]

If you need leading 0's, I think you'll have to define the field as text and use Format.

 

[Gasman]

Probably best to put in table Format if you are going to use it a lot.?
Here it is in Form format.

 

[Manicduck]

Hi Gasman and jdraw, the Field is set to short text and I have tried format in the table, form and report, separately and as a combination of some and all..... unfortunately, all to no effect.

coincidentally, I notice that the code to increment the number appears to have stopped at 10, so I now have 2 number 10s, which is not as was intended, I will remove the formatting to see if that helps

 

[Manicduck]

Ugh! Formatting removed and sequential number stops at 10 and returns 10 for each successive record

 

[Minty]

I personally don't think that storing a number as text is a very good idea.
You could have simply used an auto number with a format set, as per Gasman's suggestion, that has the benefit that if you ever exceeded 999 you could simply adjust the format in the table and it would automatically add further leading zero's.

 

[Manicduck]

Hi Minty, as I said, my knowledge of Access is at best basic, but I have experienced problems in the past with autonumbers in that they are not always sequential, if a user cancels for instance, data is deleted

 

[plog]

I personally don't think that storing a number as text is a very good idea.

Nothing personal about it, its a terrible idea. And Mani is experiencing why.

Text and numbers don't compare the same. This is why your set up will always say "10" is the next number from new records here on out.

Text Sorting:

"Z" > "Y"
"ZC" > "ZBZ"
"5">"4"
"5">"400"

Text sorting compares character by character until it finds a greater value. So in the last example the first character of the first value is greater than the first character of the second value. When you work with text "5" comes after "400". That's what's happening to your data. The highest value in your table is "9", your code then converts it a number (9) and adds 1 to it to make 10 then puts it in the database as "10".

You need to store numbers as numbers and text as text. Best yet, just use autonumbers.

 

[Minty]

Hi Minty, as I said, my knowledge of Access is at best basic, but I have experienced problems in the past with autonumbers in that they are not always sequential, if a user cancels for instance, data is deleted

And I'm going to ask the $64,000 question, why is that a problem? Unless this is an official tax record, it's merely a unique reference, doesn't matter a jot if there is one missing.

 

[Manicduck]

Hi guys, thanks for the info re txt & numbers, I can see the reasoning here.

It is not a tax record, but will be subject to similar oversight / auditing, but not to the extent that an audit trail is required. If an item is missing, it will raise all sorts of questions, which is a path I would rather not go down.

Not sure why the field was set to text in the first place as this is a part of the project I have inherited.

 

[bob fitz]

Perhaps you could save it as number. Use the code you mentioned in an earlier post to add 1 to it for each new record and set format at table level as Gasman suggested.

 

[Minty]

Hi guys, thanks for the info re txt & numbers, I can see the reasoning here.

It is not a tax record, but will be subject to similar oversight / auditing, but not to the extent that an audit trail is required. If an item is missing, it will raise all sorts of questions, which is a path I would rather not go down.

Not sure why the field was set to text in the first place as this is a part of the project I have inherited.

If that's the case, often the best way is to simply validate the entries and if they want to cancel the entry the user has to put in an explanation, which you record and mark the record as deleted/cancelled.

 

[jdraw]

If you need some sort of code/identifier and it is basically for display only, then use a text field.
Use a number if your usage involves arithmetic.

Autonumbers are intended for unique identification of records in a table.

Basic questions:
Why does it have to be sequential?
Why does it have to have leading 0's?

Research DMax(someNumber) + 1 on the forum.

 

[Manicduck]

@bob fitz, I have tried that, unfortunately, I seem to have broken something as the code doesn’t seem to Run with the field set to a number, it just returns 0

 

[Manicduck]

Hi @jdraw, it needs to be sequential to make sure that there is no wrong doing, think of it as an invoice number.

The leading 0s are a formatting choice

as Mentioned to @bob fitz, I have broken something by changing the field type to number, and with it set to text, it will only count to 10

 

[bob fitz]

Try:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.[Report_ID]= 0 Then
       Me.[Report_ID] = Nz(DMax("[Report_ID]", "tbl_Report"), 0) + 1
    End If
End Sub

 

[Manicduck]

@bob fitz, that is great, thank you, have formatted in the table field and this gives the answer I am looking for!

I take it that the change of syntax is because my example was dealing with a txt field?

once again, thank you

 

[bob fitz]

@bob fitz, that is great, thank you, have formatted in the table field and this gives the answer I am looking for!

I take it that the change of syntax is because my example was dealing with a txt field?

once again, thank you

It was needed because Number field have a default setting of 0, so the old code would not work. You needed the code I suggested or you need to remove the Default setting of 0 and use the old code.

Glad you've got it working

 

[Gasman]

Hi Gasman and jdraw, the Field is set to short text and I have tried format in the table, form and report, separately and as a combination of some and all..... unfortunately, all to no effect.

coincidentally, I notice that the code to increment the number appears to have stopped at 10, so I now have 2 number 10s, which is not as was intended, I will remove the formatting to see if that helps

Ah, mine is a number ? in both cases.
The pics I showed was just using the ID autonumber.
In the table I have as below, but I have just discovered that Field1 still appears without leading zeroes unless the form's format is used.? though shows in the table and query OK, just not form and report?
So now I am puzzled as to the benefit, if the format is in a table?

 

[jdraw]

Here's some other info.