# Converting to Multiples of Ten

1. 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")))`

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

Thanks

2. 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. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
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.

4. 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

5. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
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

6. 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

7. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
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```

8. 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")`

9. 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

10. Virtually Inert Person
Windows 10 Access 2016
Join Date
Jun 2014
Location
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.

11. 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

12. 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

#### 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