Results 1 to 13 of 13
  1. #1
    hbtousa is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    8

    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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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 online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    ?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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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 online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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 online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    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
    52,822
    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 online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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
    52,822
    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,442
    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
  •  
Other Forums: Microsoft Office Forums