Access query that needs number rounded to nearest multiple of 5

[Alpacian]

I am trying to round a number for example 21.4 to the nearest multiple of 5 - i.e. 20 or 25 and can not seem to get any of the roundup or rounddown or ceiling functions to work - always error with function not availble?? Help.

 

[AnonGod]

Just browsing forums and wanted to give quick feedback. Dunno for sure if this'll work in access, but vbs does.
This'll round up only for now. Can work in more math to get it to go up or down.
-Andrew

VBS
dim x
dim answer
x = 20
if (x mod 5) > 0 then
answer = ((x \ 5) * 5) +5
Else
answer = (x \ 5) * 5
End If

Try this for Access
IIF((x mod 5) > 0,((x \ 5) * 5) +5,(x \ 5) * 5)

 

[AnonGod]

Another thought - try this for rounding to nearest 5.
-Andrew

IIF((x mod 5) > 0,IIF((x mod 5) >= 2.5,((x \ 5) * 5) + 5,(x \ 5) * 5),(x \ 5) * 5)

 

[Alpacian]

Thank you, tried the second iif option in the query and it worked like a charm. Was trying to group ages by 5's for a crosstab query.