rounding up a number

F

fishqqq

Can someone tell me how to get a number value in a field to round up
as follows:

user inputs 18.333
format rounds up to 18.50


user inputs 18.222
format rounds to 18.00

thanks
Steve
 
J

John W. Vinson

Can someone tell me how to get a number value in a field to round up
as follows:

user inputs 18.333
format rounds up to 18.50


user inputs 18.222
format rounds to 18.00

thanks
Steve

To round to the nearest .5, multiply by 2, round to an integer, and divide by
2:

Round(2*[field], 0)/2
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
F

fishqqq

Can someone tell me how to get a number value in a field to round up
as follows:
user inputs 18.333
format rounds up to 18.50
user inputs 18.222
format rounds to 18.00
thanks
Steve

To round to the nearest .5, multiply by 2, round to an integer, and divide by
2:

Round(2*[field], 0)/2
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Great tks John
 
F

fishqqq

Can someone tell me how to get a number value in a field to round up
as follows:
user inputs 18.333
format rounds up to 18.50
user inputs 18.222
format rounds to 18.00
thanks
Steve

To round to the nearest .5, multiply by 2, round to an integer, and divide by
2:

Round(2*[field], 0)/2
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

I just got around to trying to make this work and i'm a little lost.
can you please tell me if I'm supposed to place the format "
Round(2*[field], 0)/2 " in the field's format property?
when I try this Access reformats it as follows:

this... Round(2*[Wgt Lbs], 0)/2 (note Wgt Lbs
is the field name)
turns to this ... "Rou"nd"(2"*[w"gt Lb"s"], 0)"/\2

Can you please tell me what i'm doing wrong?
thank you
 
F

fishqqq

To round to the nearest .5, multiply by 2, round to an integer, and divide by
2:
Round(2*[field], 0)/2
--
             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

I just got around to trying to make this work and i'm a little lost.
can you please tell me if I'm supposed to place the format  "
Round(2*[field], 0)/2    " in the field's format property?
when I try this Access reformats it as follows:

this...                      Round(2*[Wgt Lbs], 0)/2     (note Wgt Lbs
is the field name)
turns to this ...        "Rou"nd"(2"*[w"gt Lb"s"], 0)"/\2

Can you please tell me what i'm doing wrong?
thank you

Also I've made sure the fields are Number fields and they are set to:
field size = single
Decimal Places = 2

thank you
 
J

John W. Vinson

I just got around to trying to make this work and i'm a little lost.
can you please tell me if I'm supposed to place the format "
Round(2*[field], 0)/2 " in the field's format property?
when I try this Access reformats it as follows:

this... Round(2*[Wgt Lbs], 0)/2 (note Wgt Lbs
is the field name)
turns to this ... "Rou"nd"(2"*[w"gt Lb"s"], 0)"/\2

Can you please tell me what i'm doing wrong?
thank you

What you're doing wrong is using the Format property. Format doesn't change
the value of a field (from 3.28 to 3.5 for example); it only changes how the
field is displayed.

My suggestion was to create an Expression taking one value and returning a
different value; this expression can be used as a calculated field by putting
it in a vacant Field cell in a Query, or as the control source of a form or
report textbox.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
F

fishqqq

I just got around to trying to make this work and i'm a little lost.
can you please tell me if I'm supposed to place the format  "
Round(2*[field], 0)/2    " in the field's format property?
when I try this Access reformats it as follows:
this...                      Round(2*[Wgt Lbs], 0)/2     (note Wgt Lbs
is the field name)
turns to this ...        "Rou"nd"(2"*[w"gt Lb"s"], 0)"/\2
Can you please tell me what i'm doing wrong?
thank you

What you're doing wrong is using the Format property. Format doesn't change
the value of a field (from 3.28 to 3.5 for example); it only changes how the
field is displayed.

My suggestion was to create an Expression taking one value and returning a
different value; this expression can be used as a calculated field by putting
it in a vacant Field cell in a Query, or as the control source of a form or
report textbox.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

John, if i want this rounding to change how the field is displayed
would I put your expression in the format of the unbound textbox?
 
J

John W. Vinson

John, if i want this rounding to change how the field is displayed
would I put your expression in the format of the unbound textbox?

You wouldn't put it in the Format period.

The Format is INCAPABLE OF DOING WHAT YOU WANT.

You're barking up the wrong tree!

Put the expression - preceded by an = sign - in the Control Source of the
textbox instead.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
F

fishqqq

You wouldn't put it in the Format period.

The Format is INCAPABLE OF DOING WHAT YOU WANT.

You're barking up the wrong tree!

Put the expression - preceded by an = sign - in the Control Source of the
textbox instead.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Thank John i think i have my head around this now.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top