Add leading zero

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Location_Num Machine Number
06 DUD006
07 DUD007
07_1 DUD07_1
08 DUD008
09 DUD009

As you can see from the examples above I need to pad the numeric value with two zero's. Why two, because the highest number we'll ever need is DUD999. However, I need to pad the number between the "DUD" and 06, 07, 07_1, 08, 09 with an additional 0.

As you can see, 07_1 did not get the additional 0 similar to the rest of the numeric values did to make it DUD007_1. This is where the problem is. If it is possible I'd like to add two zeros to numeric values of 1 - 9, and one zero to numeric values of 10 - 99. Thus, if I had a value of 1 – 9 ( 1_1 - 9_1, I need to have them be 001, 002, 003, 004, (001_1, 002_1, 003_1, 004_1 if there are underscores) and for numeric values 10 - 99, I'd like to see 010, 011, 012, 013, 014 (010_1, 011_1, 12_1, 013_1 if there are underscores).

Is this possible? If it is HOW?????

Thanks,....
 
Location_Num Machine Number
06 DUD006
07 DUD007
07_1 DUD07_1
08 DUD008
09 DUD009

As you can see from the examples above I need to pad the numeric value with two zero's. Why two, because the highest number we'll ever need is DUD999. However, I need to pad the number between the "DUD" and 06, 07, 07_1, 08, 09 with an additional 0.

As you can see, 07_1 did not get the additional 0 similar to the rest of the numeric values did to make it DUD007_1. This is where the problem is. If it is possible I'd like to add two zeros to numeric values of 1 - 9, and one zero to numeric values of 10 - 99. Thus, if I had a value of 1 – 9 ( 1_1 - 9_1, I need to have them be 001, 002, 003, 004, (001_1, 002_1, 003_1, 004_1 if there are underscores) and for numeric values 10 - 99, I'd like to see 010, 011, 012, 013, 014 (010_1, 011_1, 12_1, 013_1 if there are underscores).

Is this possible? If it is HOW?????

Thanks,....
Click to expand...

Eeeuw... ugly composite keys! This difficulty is one reason most
experts hate them.

Try:

MachineNumber: "DUD" & String(4 - InStr([Location_Num] & "_", "_"),
"0") & [Location_Num]
 
Back
Top