AnsweredAssumed Answered

Solution for assigning positions in a table and keeping zeros

Question asked by KellyFisher on Jul 16, 2014
Latest reply on Jul 16, 2014 by philmodjunk

Title

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!

Outcomes