Round up or down in .5 increments

[Guest]

I need to be able to round a number either up or down in .5 increments. The
number is in the Currency Format in Access. So, if the number is 18.3, I
need to be able to either round down to 18 or round up to 18.5.

I found the Fix and Int functions which will truncate to value of 18. I have
tried using Round() but it just rounds to the next whole number.

Any help would be so appreciated!

 

[Allen Browne]

Double the value, round the number, and half the result:

Me.[MyField] = Round(2 * Me.[MyField], 0) / 2

 

[Guest]

Thanks for the response. This helps me some of the time. What I need to
do is allow the user to either round up or down.

So, if a number is 18.3, your example would round up to 18.5 which in most
cases will work but I also need to allow the user to choose to round down to
18.

I can't find a way to round down other than truncating like Fix or Int which
would not work for a number such as 18.8 because I would not want to truncate
to 18 but rather round down to 18.5. Any ideas or suggestions?

Double the value, round the number, and half the result:

Me.[MyField] = Round(2 * Me.[MyField], 0) / 2

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I need to be able to round a number either up or down in .5 increments.
The
number is in the Currency Format in Access. So, if the number is 18.3, I
need to be able to either round down to 18 or round up to 18.5.

I found the Fix and Int functions which will truncate to value of 18. I
have
tried using Round() but it just rounds to the next whole number.

Any help would be so appreciated!

 

[Allen Browne]

You will have to build in some kind of MsgBox() if you want to ask the user
whether to round up or down.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the response. This helps me some of the time. What I need
to
do is allow the user to either round up or down.

So, if a number is 18.3, your example would round up to 18.5 which in most
cases will work but I also need to allow the user to choose to round down
to
18.

I can't find a way to round down other than truncating like Fix or Int
which
would not work for a number such as 18.8 because I would not want to
truncate
to 18 but rather round down to 18.5. Any ideas or suggestions?

Double the value, round the number, and half the result:

Me.[MyField] = Round(2 * Me.[MyField], 0) / 2

I need to be able to round a number either up or down in .5 increments.
The
number is in the Currency Format in Access. So, if the number is
18.3, I
need to be able to either round down to 18 or round up to 18.5.

I found the Fix and Int functions which will truncate to value of 18. I
have
tried using Round() but it just rounds to the next whole number.

Any help would be so appreciated!

 

[Guest]

I can do that. I plan on allowing the user to choose to round up or down and
based upon their selection run the appropriate function.

What I'm having trouble with is figuring out how to write something that
will round up to a .5 increment if the number is 18.1 or 18.2 for instance or
round down if the number is 18.8 to 18.5 instead of to 19.

I am going around in circles on this. I am experienced working with Access
but not in writing any code using VBA.

You will have to build in some kind of MsgBox() if you want to ask the user
whether to round up or down.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the response. This helps me some of the time. What I need
to
do is allow the user to either round up or down.

So, if a number is 18.3, your example would round up to 18.5 which in most
cases will work but I also need to allow the user to choose to round down
to
18.

I can't find a way to round down other than truncating like Fix or Int
which
would not work for a number such as 18.8 because I would not want to
truncate
to 18 but rather round down to 18.5. Any ideas or suggestions?

Double the value, round the number, and half the result:

Me.[MyField] = Round(2 * Me.[MyField], 0) / 2

I need to be able to round a number either up or down in .5 increments.
The
number is in the Currency Format in Access. So, if the number is
18.3, I
need to be able to either round down to 18 or round up to 18.5.

I found the Fix and Int functions which will truncate to value of 18. I
have
tried using Round() but it just rounds to the next whole number.

Any help would be so appreciated!

 

[Allen Browne]

Use Int(xx) to round down.

Use -Int(-xx) to round up.

(That's with the doubling and halving of course.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I can do that. I plan on allowing the user to choose to round up or down
and
based upon their selection run the appropriate function.

What I'm having trouble with is figuring out how to write something that
will round up to a .5 increment if the number is 18.1 or 18.2 for instance
or
round down if the number is 18.8 to 18.5 instead of to 19.

I am going around in circles on this. I am experienced working with
Access
but not in writing any code using VBA.

You will have to build in some kind of MsgBox() if you want to ask the
user
whether to round up or down.

Thanks for the response. This helps me some of the time. What I
need
to
do is allow the user to either round up or down.

So, if a number is 18.3, your example would round up to 18.5 which in
most
cases will work but I also need to allow the user to choose to round
down
to
18.

I can't find a way to round down other than truncating like Fix or Int
which
would not work for a number such as 18.8 because I would not want to
truncate
to 18 but rather round down to 18.5. Any ideas or suggestions?

:

Double the value, round the number, and half the result:

Me.[MyField] = Round(2 * Me.[MyField], 0) / 2

I need to be able to round a number either up or down in .5
increments.
The
number is in the Currency Format in Access. So, if the number is
18.3, I
need to be able to either round down to 18 or round up to 18.5.

I found the Fix and Int functions which will truncate to value of
18. I
have
tried using Round() but it just rounds to the next whole number.

Any help would be so appreciated!

 

[Guest]

OK. Thank you so much for your help. I'll try this. I have to cycle thru
each instance of (Field Name).1 thru (FieldName).9 to get the results I need.

Use Int(xx) to round down.

Use -Int(-xx) to round up.

(That's with the doubling and halving of course.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I can do that. I plan on allowing the user to choose to round up or down
and
based upon their selection run the appropriate function.

What I'm having trouble with is figuring out how to write something that
will round up to a .5 increment if the number is 18.1 or 18.2 for instance
or
round down if the number is 18.8 to 18.5 instead of to 19.

I am going around in circles on this. I am experienced working with
Access
but not in writing any code using VBA.

You will have to build in some kind of MsgBox() if you want to ask the
user
whether to round up or down.

Thanks for the response. This helps me some of the time. What I
need
to
do is allow the user to either round up or down.

So, if a number is 18.3, your example would round up to 18.5 which in
most
cases will work but I also need to allow the user to choose to round
down
to
18.

I can't find a way to round down other than truncating like Fix or Int
which
would not work for a number such as 18.8 because I would not want to
truncate
to 18 but rather round down to 18.5. Any ideas or suggestions?

:

Double the value, round the number, and half the result:

Me.[MyField] = Round(2 * Me.[MyField], 0) / 2

I need to be able to round a number either up or down in .5
increments.
The
number is in the Currency Format in Access. So, if the number is
18.3, I
need to be able to either round down to 18 or round up to 18.5.

I found the Fix and Int functions which will truncate to value of
18. I
have
tried using Round() but it just rounds to the next whole number.

Any help would be so appreciated!