1 Reply Latest reply on Jul 16, 2014 2:04 PM by philmodjunk

    Solution for assigning positions in a table and keeping zeros

    KellyFisher

      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!

        • 1. Re: Solution for assigning positions in a table and keeping zeros
          philmodjunk

               I am truly puzzled by what you are trying to do here and why. You appear to be using fields where you might be better off with a set of records. (each row could be a record in a related table.)

               But I don't follow the logic to your use of text functions to extract different sets of digits from the same value in what I must assume is a single field.

               You might use a different field for each digit or even a repeating field instead, though the why of all this escapes me.