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

    2 Rounding Rules On 1 Table

    This is a fairly simple question, but I don't know how simple the answer may be... Or if it's even possible. I have one table with field [Quantity] that I would like to apply rounding rules to using this code or something similar:


    Code:
    If Me.Quantity >= 15 Then
    Me.Quantity = Round(Me.Quantity,1)
    Else
    Me.Quantity = Round(Me.Quantity,3)
    Another potential idea is this:
    Code:
    sSQL = "UPDATE tbl_Formulas SET tbl_Formulas.Quantity = Round(Quantity,1) WHERE tbl_Formulas.Quantity >= 15"
    sSQL = "UPDATE tbl_Formulas SET tbl_Formulas.Quantity = Round(Quantity,3) WHERE tbl_Formulas.Quantity < 15"
    The idea is to round numbers greater than 15 to the nearest tenth and numbers less than 15 to the nearest thousandth. I've tried toying with the field settings (double, standard, fixed, etc) but nothing works. If it isn't possible I'd still like to know. Thanks for any help.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    Me.Quantity = ROUND(Me.Quantity, Iif(Abs(Me.Quantity) < 15, 3, 1))

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Thanks for the idea. The question I'm getting at is how do I get a table to accept two different decimal places? i.e. 4 becomes 4.000 and 15.000 becomes 15.0
    [Quantity]
    1.050
    2.700
    15.4
    17.0
    12.979

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sounds like the data type is Number/Integer or Long Integer. Try Currency.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    I tried that. It adds "$" to every number and makes everything default to 2 decimal places. I'm trying to have all numbers above 15 to be one decimal place and all numbers below 15 to be three decimals. (on the same table field)

    Currency
    [Quantity]
    $100.00
    $7.00

    Currently I am set to Number / Double / Standard / 3
    [Quantity]
    5.000
    20.000
    15.123

  6. #6
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Try this;

    IIf([TestField]>=15,Format([TestField],"0.0"),Format([TestField],"0.000"))

    Replace TestField with your actual field name of course.

  7. #7
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Just noticed that Format will "round" differently than Round, so this may be a factor in whether or not this will work for you.

    Click image for larger version. 

Name:	Capture18.PNG 
Views:	14 
Size:	2.7 KB 
ID:	34172

  8. #8
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Maybe this;

    IIf([TestField]>=15,Format(Round([TestField],1),"0.0"),Format(Round([TestField],3),"0.000"))

  9. #9
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Thanks Sean. That produced the same result as the other solutions. The aforementioned codes work i.e. 15.167 becomes 15.200 but since the table defs are set to 3 decimals I get the extra two 0's. I'm starting to think that there isn't a way to have 2 different decimal place settings on the same field.

  10. #10
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Quote Originally Posted by lccrews View Post
    That produced the same result as the other solutions.
    Which one did you try, post #6 or post #8?

    Quote Originally Posted by lccrews View Post
    I'm starting to think that there isn't a way to have 2 different decimal place settings on the same field.
    There isn't, but to me this is not a data storage problem, it's a data display problem. I have tested on my end and can display the data in the manner that you seem to be wanting.

    Here is the data (Number field, Size = Double, Decimal Places = Auto)


    Click image for larger version. 

Name:	Capture19.PNG 
Views:	14 
Size:	5.4 KB 
ID:	34174



    Here is the result;

    Click image for larger version. 

Name:	Capture20.PNG 
Views:	14 
Size:	6.2 KB 
ID:	34175

  11. #11
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    I guess it was the combination in which I was applying them. I have been applying them under condition double/standard/3.... Silly mistake. This works perfectly. You're absolutely right on the issue being about display rather than storage. Thanks again for the help.

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

Similar Threads

  1. Validation Rules
    By hinkwale in forum Access
    Replies: 2
    Last Post: 01-21-2015, 07:06 PM
  2. table relationships and business rules - I'm stuck!
    By sansui88 in forum Database Design
    Replies: 3
    Last Post: 03-03-2014, 04:01 PM
  3. Field rules/validation rules
    By sk88 in forum Access
    Replies: 14
    Last Post: 02-26-2012, 01:03 PM
  4. Replies: 2
    Last Post: 10-23-2010, 09:38 AM
  5. Replies: 3
    Last Post: 04-04-2010, 05:26 PM

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