Incrementing Number-Leading Zeros

[brad.goldberg]

Hey All,

I know this has been addressed in a few different conversations but
none are exactly what I am trying to do, bear with me I am an access
Newbie.

Basically I have a form that assigns Run Numbers to each record (EMS
agency, each record is a Run Number, versus like a PO number or Work
Order..).

Right now I have a field which I call "displayedRunNumber" stored in a
table as a number. The default value in the table is 0.

In the form where the counter is viewed I have a before update code of:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!displayedRunNumber = Nz(DMax("displayedRunNumber", "fields"),
1000) + 1

End If

End Sub

This basically starts displayedRunNumber at 1001 for the first record
in the form. The code also works nicely because the number isnt
generated until the user clicks a Save button I have on the form to
ensure he/she fills in all the information before he gets the Run
Number he/she needs to finish a report.

**Here's the problem. instead of 1001, I need the starting number to be
0001 and increment from there. I thought it would be OK my way, but the
law says it has to be 0001. I still want the same functionality as far
as the number generating after the record is saved. The problem is the
leading zeros, I guess the format of the displayedRunNumber has to text
I guess.

Also I would prefer not to force the number into looking the way I want
it because I need to be able to search for say 0018 and find it.

Any help would be SO GREATLY appreciated!! I am very new at all this so
if you have code or suggestions please tell me where it is supposed to
go as people often reference code and I have no idea where it goes.

Thanks so much everyone,

Brad G.

 

[Pieter Wijnen]

I'd still store it as a number & use formatting to display it as '0001'
any search would then maximum require the use of the Val, Clng, Fix or other
text-to-number conversion functions to work
If you *insist* on storing it as text:
Me!displayedRunNumber = Format(Clng(Nz(DMax("displayedRunNumber",
"fields"),"0")) + 1,"0000")

Pieter

 

[brad.goldberg]

How could I do it the way you recommend...

Just be specific please as I am a huge Newbie and often dont know where
to put code when people rattle it off.

Thanks so much

 

[Arvin Meyer [MVP]]

Brad,

Look at the code that Pieter changed for you. Adding the format function
displays the number as text (0123), but still stores it as a number (123).
When you increment you are incrementing the real number, then formating it
with leading zeros.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access