Solution for assigning positions in a table and keeping zeros
KellyFisher Jul 16, 2014 11:26 AMTitle
Solution for assigning positions in a table and keeping zeros
Post
Okay I have been looking for awhile on a solution to this and can't find. I thought I found a work-around but it's not working quite right.
I have a database that is calculating the total hours on a project and the hours are broken down into 5 groups and then a total of those 5 groups (so 6 calculations in all). On one layout is am putting in all the information, but on another is a form to fill it out. Where the hours all they want the digits in 5 distinct boxes.
For example, PartA= 16 hrs; PartB= 256 hrs; PartC=7 hrs; PartD=635 hrs; PartE=205 hrs; TotalPartABCDE = 1119 hrs
PartA | 0 | 0 | 0 | 1 | 6 |
PartB | 0 | 0 | 2 | 5 | 6 |
PartC | 0 | 0 | 0 | 0 | 7 |
PartD | 0 | 0 | 6 | 3 | 5 |
PartE | 0 | 0 | 2 | 0 | 5 |
TotalPartABCDE | 0 | 1 | 1 | 1 | 9 |
For the workaround, I did this with each digital spot having it's own field:
- position 1 calculation = Right(PartA;1) and this yielded 6
- position 2 calculation pt1 = Right(PartA;2) and this yielded 16
- position 2 calculation pt2 = Left(position 2 calculation pt1;1) and this yielded 1
- and so on through position 3, 4, and 5
But since my numbers don't have leading zeros, it just brings back the furthers left position, so I am seeing the wrong digits (in green) instead of just seeing a zero
PartA | 1 | 1 | 1 | 1 | 6 |
PartB | 2 | 2 | 2 | 5 | 6 |
PartC | 7 | 7 | 7 | 7 | 7 |
PartD | 6 | 6 | 6 | 3 | 5 |
PartE | 2 | 2 | 2 | 0 | 5 |
TotalPartABCDE | 1 | 1 | 1 | 1 | 9 |
I then tried people's solution of putting doing the calculation Right ( "00000" & PartA; 5 ) thinking it would yield 00016 but it just shows 16. I tried it in the original field and a new field that pulled from the original field [ PartA copy = Right ( "00000" & PartA; 5 ) ]. But no luck.
i know I am making this more difficult than it needs to be.
Help!