I have an ID number as ABCD-1234-123.
I use an Excel UI to add data to Access table.
The ID is division (alpha), department (four digits in the middle) and the
last three digits are sequence.
When I update this record, I actually want to add a new row to the table and
increment the sequence for each update.
So the initial record is ABCD-1234-001.
First update will be ABCD-1234-002.
Second update will be ABCD-1234-003 and so on.
Can some one please help with making sure that I keep the leading zeros.
Riight now, I cannot seem to do it.

Thanks.

Try using a text function to apply the specific formatting your interested in.
For example you can re-assemble the full ID string (if need be) using
CONCATENATE. Along the way you could use the TEXT function to ensure the
leading zeros exist (eg. if your last three numbers at some point appear as a
simple "2" in say cell M12, using =TEXT(M12,"00#") will ensure the retention
of those leading zeros..
For more hints on the use of text functions see "Excel Basics to Blackbelt"
( www.excel-blackbelt.com )


"omsoft" wrote:

I have an ID number as ABCD-1234-123.
I use an Excel UI to add data to Access table.
The ID is division (alpha), department (four digits in the middle) and the
last three digits are sequence.
When I update this record, I actually want to add a new row to the table and
increment the sequence for each update.
So the initial record is ABCD-1234-001.
First update will be ABCD-1234-002.
Second update will be ABCD-1234-003 and so on.
Can some one please help with making sure that I keep the leading zeros.
Riight now, I cannot seem to do it.

Thanks.

Thanks Elliot, but I was looking for a solution in VBA and not Excel formulae.

I found one, where I can get lengh of the cosecutive number and then force
add leading zeroes.

"Elliot" wrote:

Try using a text function to apply the specific formatting your interested in.
For example you can re-assemble the full ID string (if need be) using
CONCATENATE. Along the way you could use the TEXT function to ensure the
leading zeros exist (eg. if your last three numbers at some point appear as a
simple "2" in say cell M12, using =TEXT(M12,"00#") will ensure the retention
of those leading zeros..
For more hints on the use of text functions see "Excel Basics to Blackbelt"
( www.excel-blackbelt.com )


"omsoft" wrote:

I have an ID number as ABCD-1234-123.
I use an Excel UI to add data to Access table.
The ID is division (alpha), department (four digits in the middle) and the
last three digits are sequence.
When I update this record, I actually want to add a new row to the table and
increment the sequence for each update.
So the initial record is ABCD-1234-001.
First update will be ABCD-1234-002.
Second update will be ABCD-1234-003 and so on.
Can some one please help with making sure that I keep the leading zeros.
Riight now, I cannot seem to do it.

Thanks.