Results 1 to 10 of 10
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    My query keeps rounding my answers. Driving me insane!

    I've attached the form that I'm working with so that you can see what I'm going through and hopefully tell me that it's a simple fix.



    My form pulls in a formula from tbl_Formulas to a tmp_Formula table to work with. From there the user inputs all kinds of information and updates it. One of those pieces of information is the [Claim]. Once they update that and click update formula (button removed from testingarea.accdb, please manually save/refresh form) and qry "1" runs to update the [DV] field. "1" is an update query that should update [DV] to = [Claim]/[ADV].

    [ADV] is a field from tbl_RawMaterial. In Query "1" I have tbl_RawMaterial.RawMaterial= tmp_Formula.RawMaterial. So I believe the numbers are being pulled correctly. The problem is this: If you place "12.5" in the claim of the first ingredient listed (which is 101114 - RIBOFLAVIN USP), run "1" and refresh the form, you get 10.00 as the [DV]. This should be [Claim]/[ADV] = 12.5/1.3 = 9.61

    I'm driving myself mad trying to figure out how/why it's rounding. I've checked the formatting of [Claim], [DV], [ADV], qry field settings, tbl field settings, nothing is adding up.
    Attached Files Attached Files

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If any of the result fields in your calculations is an integer or long, no decimal is possible.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    DV is a Number/Long Integer field in the table; it can't hold a decimal. Try Currency if 4 digits is enough accuracy.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    omg.... Davegri/Paul, thank you. None of the variables in the query are long integer, but the field that is being updated (which I overlooked) is long inteeger. How do I change it's setting. In the query that makes that table, the field is set to Standard. However when I look at te design view of the table, it reverts to Long Integer.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The query is irrelevant. You have to change it in table design view.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    But as a temp table, I am unable to change the field [DV] from Long Integer to Double. It won't let me. I've tried to delete the table and run the query again to re-create it. That didn't solve it. It's like the only way for me to change the attributes is via the query, but I have already set [DV] to Standard in the query.

  7. #7
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    GOT IT! I simply set the field to DV: "0" instead of DV: 0

    This set the field to text instead of a number and the query worked perfectly.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The query is just an update query, the table already exists. I just changed the field in your sample without a problem. Make sure no object (form, etc) is open that's bound to that table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by lccrews View Post
    GOT IT! I simply set the field to DV: "0" instead of DV: 0

    This set the field to text instead of a number and the query worked perfectly.
    Where exactly? There's no make table query in the sample. Text will display fine but might not work as expected in any math related operation down the road.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    The make table query wasn't attached as I didn't know it was relevant. Your comments alerted me to the problem. I'm not too concerned with the being a problem down the road. This is a temp table. Once the user finishes making changes, the values are transferred from tmp_Formula to tbl_Formulas. There the attributes are set correctly and I just tested that theory by transferring the records from one table to the other. It's good to go as a number field now.

    Seriously, thank you so much for helping with your comments Dave and Paul. You two have saved my bacon yet again!
    Last edited by lccrews; 04-09-2018 at 10:42 AM. Reason: Saying thanks to Dave and Paul

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Rounding
    By RayMilhon in forum Queries
    Replies: 2
    Last Post: 10-27-2017, 02:31 PM
  2. Replies: 6
    Last Post: 04-03-2014, 09:04 PM
  3. Rounding a number in a query
    By libraccess in forum Queries
    Replies: 3
    Last Post: 08-25-2012, 09:13 AM
  4. SELECT Query, Driving Me Nuts!!
    By mystifier in forum Queries
    Replies: 4
    Last Post: 04-21-2011, 04:00 PM
  5. Pls help... driving me insane!
    By Maverick1501 in forum Reports
    Replies: 1
    Last Post: 03-31-2010, 06:26 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums