By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

How can I pad a number to make sure it's 5 chars in length?

P: n/a
jj
example is I'm finding the max number within a certain criteria, and then
adding one to that. However, most IDs are something like K00001 or R00223

So I want the next ID to be either K00002 or R00224

Unfortunately Access is stripping out the leading 0s leaving me with an ID
of K3 or R224.
With PHP I'm able to do something like:
$newid = str_pad($row[0] + 1, 5, "0", STR_PAD_LEFT);
To make sure the ID is always 5 chars. Here's part of what I have in my
INSERT statement now:

'" & Left(rst!IDNUM, 2) & rstMax!MaxNum + 1 & "'

So it'd be nice to have something like:

'" & Left(rst!FIDNUM, 2) & newid & rstMax!MaxNum + 1 & "' (where newid is
the padded 0s if necessary)
Is there such a thing I can do in Access/VB?

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"jj" <jj@test.net> wrote in message
news:Cx*******************@twister.rdc-kc.rr.com...
example is I'm finding the max number within a certain criteria, and then
adding one to that. However, most IDs are something like K00001 or R00223

So I want the next ID to be either K00002 or R00224

Unfortunately Access is stripping out the leading 0s leaving me with an ID
of K3 or R224.
With PHP I'm able to do something like:
$newid = str_pad($row[0] + 1, 5, "0", STR_PAD_LEFT);
To make sure the ID is always 5 chars. Here's part of what I have in my
INSERT statement now:

'" & Left(rst!IDNUM, 2) & rstMax!MaxNum + 1 & "'

So it'd be nice to have something like:

'" & Left(rst!FIDNUM, 2) & newid & rstMax!MaxNum + 1 & "' (where newid is
the padded 0s if necessary)
Is there such a thing I can do in Access/VB?


Format(rstMax!MaxNum + 1,"00000")

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #2

P: n/a
Hi, JJ.

You can use the Format function to pad leading zeros on a value that has a
varying number of digits. For an example, see "How to format a string with
varying numbers of leading zeros" on this Web page:

http://www.access.qbuilt.com/html/vba.html

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
"jj" <jj@test.net> wrote in message
news:Cx*******************@twister.rdc-kc.rr.com...
example is I'm finding the max number within a certain criteria, and then
adding one to that. However, most IDs are something like K00001 or R00223

So I want the next ID to be either K00002 or R00224

Unfortunately Access is stripping out the leading 0s leaving me with an ID
of K3 or R224.
With PHP I'm able to do something like:
$newid = str_pad($row[0] + 1, 5, "0", STR_PAD_LEFT);
To make sure the ID is always 5 chars. Here's part of what I have in my
INSERT statement now:

'" & Left(rst!IDNUM, 2) & rstMax!MaxNum + 1 & "'

So it'd be nice to have something like:

'" & Left(rst!FIDNUM, 2) & newid & rstMax!MaxNum + 1 & "' (where newid is
the padded 0s if necessary)
Is there such a thing I can do in Access/VB?


Nov 13 '05 #3

P: n/a
jj
Wow, thank you so much for the quick response and easy fix!
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:2n************@uni-berlin.de...

Format(rstMax!MaxNum + 1,"00000")

--

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.