Results 1 to 15 of 15
  1. #1
    melhado
    Guest

    Trailing Zeros Display and Storage

    Afternoon,

    In my form's text boxes I need to be able to input a number (field is Number|Double data type, formatted General, 3 decimal places), i.e., 1.230, and be able to store the trailing 0 in the table and display it on the report. The control in the report is a concatenated field -- which, to my thinking, storing it in the table is the best way to accommodate this request.

    Problem is that I keep losing the trailing zero when it goes to the next field; it is not being stored in the table, either. To make it look right on the form is no good, as it will not come up on the report properly. I have scoured the help, MS KB, TechNet, etc, and cannot find much on this topic, other than formatting, which I had already done.

    Any ideas on how I can do this?

    thanks

    'dave

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trailing Zeros Display and Storage

    Try putting .000 in the format property of the field in the report.

  3. #3
    melhado
    Guest

    Re: Trailing Zeros Display and Storage

    It's a concatenated field:
    <pre>=[INS_Wire_AVG] & "mm"</pre>


    I am not sure of the syntax for the format function in this context .. would it be ...

    thanks for the quick response on a Friday afternoon

    'dave

    =Format([INS_Wire_AVG]#.000) & "mm"

    ?? There is no way to do it in the table???

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trailing Zeros Display and Storage

    Tables store data. Formats are properties of fields or variables or controls. You can format the table field to display three decimals, but the entire number is what is stored in it, regardless of the display format.
    Charlotte

  5. #5
    melhado
    Guest

    Re: Trailing Zeros Display and Storage

    I realize that the tables store data. I am trying for a simple solution to this issue.

    The data I am working with came from an Excel worksheet , in which most of the rounding and storage issues were resolved prior to import to Access. However 2 fields were rounded to 4 decimal places, instead of the three they were supposed to be. Of course, this was never noticed until after the database was split and the front end delivered, and with an impending deadline.

    It turns out it is not enough to just format it in the form and report (for which the data controls are concatenated -- I am a little foggy on the proper syntax of the Format function in this context; it is number field.). The data in the fields now has to be rounded as well as display only 3 decimal places and hold the trailing zero.

    Since it is used in a few places, my thoughts turned to making it 3 decimal places in the table, as that is how data will be entered.

    I figure I will have to somehow round the columns and replace them, then use the format properties to make the trailing 0 hold -- in the table, form, and in the concatenated field of the report.

    Any ideas or suggestions are welcome.

    Thank you,

    'dave

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trailing Zeros Display and Storage

    Use an update query on the table and put the following expression in the update to cell on the query grid. This assumes that the name of the field is MyField, which you should replace with the actual field name.

    Round([MyField],3)

    Just be sure you understand how the rounding works. Access and Excel do *not* round the same way on a determinant of 5.
    Charlotte

  7. #7
    melhado
    Guest

    Re: Trailing Zeros Display and Storage

    Thank you for that update solution. I will check on the Round function details in Access.

    Meanwhile, for the report formatting on the concatenated field, would it be similar syntax, i.e.:

    <font color=blue>=Format([fieldname] & "mm", #.000)</font color=blue>

    to get it to show the trailing zeros?

    thanks again,

    'dave

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trailing Zeros Display and Storage

    Sorry, I'm lost on the "mm" in your expression. Is that something you want concatenated to the formatted value? If so, do it like this:

    <font color=6495ed>=Format([fieldname], #.000) & "mm"</font color=6495ed>
    Charlotte

  9. #9
    melhado
    Guest

    Re: Trailing Zeros Display and Storage

    Charlotte,

    YOU DA BEST <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    You guessed it perfectly. It was where to put the "mm" that got me confused, but no more.

    Back to the issue on ROUND in Access not rounding up on .5, as Excel does, is there any way to force it? As I have 4 decimal places now and want to make it 3, there will be no .51 to make it go up.

    Any Math PhDs out there that know which is actually correct, Excel or Access? Curious.

    Thanks again,

    'dave

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trailing Zeros Display and Storage

    In this post, I posted some code I wrote for Access 97, which didn't support the Round() function, to allow you to round up. Take a look and see if that will serve your purpose. Otherwise, you'll have to write your own. I'm sure this could be simplified, but I haven't bothered.
    Charlotte

  11. #11
    melhado
    Guest

    Re: Trailing Zeros Display and Storage

    It looks like it might do the job I want. Right now, they are trying to decide whether the customer's specs call for rounding down on .5 or up. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    This is just a one shot thing that needs to be done to accommodate an oversight in converting the data from inches to mm in Excel, before importing to my database.

    I really appreciate your help, and it makes me feel a little better that someone else faced a similar situation.

    BTW, your code was easy to follow and nicely commented, thank you again.

    'dave

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trailing Zeros Display and Storage

    I have to maintain my own code. Extensive commenting makes that task much easier. <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>
    Charlotte

  13. #13
    melhado
    Guest

    Re: Trailing Zeros Display and Storage

    You are right, your extnesive commenting does make the task easier <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    thanks again,

    'dave

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trailing Zeros Display and Storage

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Maybe I could leave the comments out when posting responses to <big>melhado</big> ...
    Charlotte

  15. #15
    melhado
    Guest

    Re: Trailing Zeros Display and Storage

    <img src=/S/hushmouth.gif border=0 alt=hushmouth width=16 height=16> I'll be good, I promise <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>.

    'dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •