Results 1 to 13 of 13

Converting to Multiples of Ten

  1. #1
    hbtousa is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    6

    Converting to Multiples of Ten

    I have a database with 3-digit fields. I have the fields as numeric and/or a string to be able to manipulate them on either way. I wan to convert each digit field to its close 10-multiple. Example
    100==>No change
    101==>Change to 100


    349==>Change to 350
    223==>Change to 220
    190==>No Change
    015==>Change to 020
    013==>Change to 010
    276==>Change to 280
    007==>Change to 010
    002==>Change to 000

    My "rule' is that numbers over 5 to change them to next ten value. Numbers below 4 to the nearest 10 value.
    The query below, does the job. However, when the field has values below 100 and the middle number is >=5, the fields looses the format. Instead of showing "060" it shows "60". For some reason it looses its format. I tried to use the AND/OR but I guess it doesn't exist on Access build event.

    Code:
    LWry: IIf(Right([KPL_Wrist_y],1)="0",[KPL_Wrist_y],IIf(Right([KPL_Wrist_y],1)>="5",CStr(CInt(Left([KPL_Wrist_y],2))+1)+"0",IIf(Right([KPL_Wrist_y],1)<="3",Left([KPL_Wrist_y],2)+"0",Left([KPL_Wrist_y],2)+"0")))
    Click image for larger version. 

Name:	Multiples.PNG 
Views:	22 
Size:	7.4 KB 
ID:	39440

    Can anyone take a look and please give me a hint?

    Thanks

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,257
    you need to clarify this rule

    My "rule' is that numbers over 5 to change them to next ten value. Numbers below 4 to the nearest 10 value.
    what if the number is 5?

    but in principle - divide by 10, round then multiply by 10

    depends what you want to do if the value is 5 but this rounds 5 up

    ?round(355/10)*10
    360

    just noticed you are using preceding zeros which means your numbers are a text datatype so you need

    ?format(round(val("015")/10)*10,"000")
    020

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,016

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    ?round(355/10)*10
    360
    but it rounds 185 to 180? Same for 285, 385, 485...
    I think a function would be more reliable. From what I can see, it seems to be a common approach for rounding to some value such as 5.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,257
    but it rounds 185 to 180? Same for 285, 385, 485...
    interesting - not noticed that before

    ?round(355/10)*10
    360 - rounds up

    ?round(365/10)*10
    360 - rounds down

    OP needs to clarify the requirement for 5

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    not exactly was it explicitly stated, but is implied
    015==>Change to 020
    You could probably take this logic and put it in some IIF statement, but I lean towards using functions in queries rather than long IIF statements.
    Disclaimer: returns nothing for numbers < 10 and is written as a test. To use in a query, function would have to accept a field and the loop would be removed.
    Code:
    Function TestRounding()
    Dim i As Long, n As Long
    
    For i = 1 To 45
    If i Mod 10 = 5 Then
        n = Round(i / 10, 0) * 10 + 5
    Else
        n = Round(i / 10, 0) * 10
    End If
    
    Debug.Print i & "  " & n
    n = 0
    Next
    
    End Function
    EDIT -If you see this before I tweak it, ignore because I broke it somewhere between testing and posting. Hold on...
    OK, issue is that I only tested to 45. It has the same rounding issue where the number that precedes 5 is 5 (e.g. 55). I have read about this before, but thought it only applied to decimals such as 255.55 > 255.60. Perhaps it is wrong to use integer or long data type. Will have to check but will soon have to bow out for the afternoon.
    Last edited by Micron; 08-14-2019 at 11:14 AM. Reason: code correction
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,257
    but is implied
    good point

    function could just be

    ?(round(355\10)-(355 mod 10>=5))*10
    360

    ?(round(365\10)-(365 mod 10>=5))*10
    370

    ?(round(5\10)-(5 mod 10>=5))*10
    10

    ?(round(4\10)-(4 mod 10>=5))*10
    0

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    I also find that the Fix function does the (implied) trick
    Code:
    For i = 10 To 255
    If i Mod 10 = 5 Then
        n = Fix(Round(i / 10, 1) * 10 + 5)
    Else
        n = Round(i / 10) * 10
    End If
    Debug.Print i & "  " & n
    n = 0
    Next
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  9. #9
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    163
    or without any IF:
    Code:
    n = Round(i / 10 + 0.01) * 10
    Debug.Print i & "  " & n
    and for hbtousa's needs,
    Code:
    LWry: Format(Round([KPL_Wrist_y] / 10 + 0.01) * 10,"000")

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,722
    Keep in mind Round function uses even/odd rule (banker's rounding).

    Round(38.5) = 38
    Round(37.5) = 38

    However, Format does not.

    Format(38.5,"00") = 39
    Format(37.5,"00") = 38
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    Format(37.5,"00") = 38
    That won't round 015 to 020 as posted
    015==>Change to 020
    Besides, you probably meant Format(37.5,"000")?
    We seem to be the only ones deciding what happens to values that end in 5.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,722
    No, what I posted is what I meant. Just examples for info. Not intended as a full answer to the question.

    However, in VBA immediate window:

    ?Format(Format("015"/10,"00")*10,"000")
    020

    ?Format(Format("185"/10,"00")*10,"000")
    190

    ?Format(Format("002"/10,"00")*10,"000")
    000

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,402
    Rounded: IIf([testnum] Mod 10=0,[testnum],IIf([testnum] Mod 10>=5,10,0)+Int([testnum]/10)*10)

    if your 'number' value is actually a string

    Rounded: IIf(clng([testnum]) Mod 10=0,clng([testnum]),IIf(clng([testnum]) Mod 10>=5,10,0)+Int(clng([testnum])/10)*10)

    this will always round up if the 'units' value is 5 or more and round down if the units value is 4 or less

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

Similar Threads

  1. Replies: 5
    Last Post: 06-07-2018, 10:33 AM
  2. removing multiples of an order number
    By mindbender in forum Access
    Replies: 6
    Last Post: 11-04-2015, 02:07 PM
  3. Print Multiples of a Report using vba
    By WickidWe in forum Reports
    Replies: 1
    Last Post: 01-15-2014, 06:51 PM
  4. Choosing Multiples in a form
    By Jackfam58 in forum Forms
    Replies: 1
    Last Post: 07-31-2012, 08:41 AM
  5. DCount for Multiples
    By Huddle in forum Access
    Replies: 2
    Last Post: 12-21-2010, 08:57 AM

Posting Permissions

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