want to get rid of decimal point

[RonnieK]

Hi. I am working with an Access 2000 database. I have a query that contains the following formula:

SubAreaOneLeadsL: [Plumbingleadspermonthl]*[subareaonepercentage]


The field, "SubareaOne percentage" (below) is a formula that uses the number of homes in an area (subareaonefsagroupnum) divided by an overall number of homes (totalhomes) as shown:

SubareaOnepercentage: [subareaonefsagroupnum]/[totalhomes]

The resulting number (SubAreaOneLeadsL) is displayed as 4.47430147679721.

I would like the resulting number to be rid of the decimal point and rounded to the next number. In the example above, the result (the way I want it) would be 5.

Can you help me with this?

If rounding is as complicated as it looks, can you steer me as to how to wind up with a number with a decimal point such as 4.7. I would be happy with that

Ron

 

[Uncle Gizmo]

http://www.consultdmw.com/rounding-numbers.htm

 

[Uncle Gizmo]

SOMETHING LIKE;-

Private Sub Command0_Click()
    txtAns = CInt(Text1) + CInt(Text3)
End Sub

Where text1 and text3 contain your values (4.47430147679721) and txtAns shows the answer as an integer.

 

[Uncle Gizmo]

With division or multiplication you have to do it slightly differently:-

Private Sub Command0_Click()
Dim intAns As Long
    intAns = Text1 / Text3
    txtAns = CInt(intAns)
End Sub

 

[Uncle Gizmo]

If you want finer, better control then the formula on the website I mentioned might well be something to try although I haven't tried it myself.

http://www.consultdmw.com/rounding.txt

 

[RonnieK]

Sorry, I am totally lost. I do not understand the syntax and I would ask where within the access query would I place this code? Withing the query editing screen in the field formula itself?

 

[Galaxiom]

There are several ways to round up depending on several factors.

This one rounds up the result of a division of integers to the next integer:

SubAreaOneLeadsL: Int([Plumbingleadspermonthl]/[subareaonepercentage]) + Sgn([Plumbingleadspermonthl] MOD [subareaonepercentage])

The first part gets the integer from the quotient while the second rounds up if there is any remainder.

Notes:
It will round a negative quotient towards zero.

It should not be used if the dividend or divisor are fractions held as Single or Double datatypes due to inaccuracies in storing base10 fractions in a base2 system.

 

[RonnieK]

Hi, Thanks, but it did indeed create error messages in all records. I don't understand about what you noted about not using it if: :"It should not be used if the dividend or divisor are fractions held as Single or Double datatypes due to inaccuracies in storing base10 fractions in a base2 system.

Does this mean that the divisors must be whole numbers?

For some reason, it was easy to import the query into excel and then format the number and currency fields where I can get rid of the decimal points and the numbers automatically show as whole numbers. This can be used successfully in a Word merge file . Unfortunately, my database is within a website built with Frontpage 2000, and I would like to display the numbers within my .asp pages. I could not find a way for the .asp pages to format the numbers. Any ideas?

 

[Galaxiom]

Hi, Thanks, but it did indeed create error messages in all records.

Sorry I put a "*" instead of a "/". You probably got an overflow error. Fixed it now.

I don't understand about what you noted about not using it if: :"It should not be used if the dividend or divisor are fractions held as Single or Double datatypes due to inaccuracies in storing base10 fractions in a base2 system.

Does this mean that the divisors must be whole numbers?

For accuracy, with this expression, yes. Single and Double use a floating point binary format. Some fractions are not precise so sometimes what would be expected as a whole number comes out wrong.

Good example:

Int(CDbl(90)*CDbl(0.7))

If you store the numbers as Decimal datatype there is no problem.

Int(CDec(90)*CDec(0.7))

I could not find a way for the .asp pages to format the numbers. Any ideas?

ASP should support the same expression.

However I would do it in the SQL.

 

[RonnieK]

Hi: In your reply, you state that you accidentally put in a "*" instead of a "/".

The expression you gave me to try was the following. I do not see the "*"
SubAreaOneLeadsL: Int([Plumbingleadspermonthl]/[subareaonepercentage]) + Sgn([Plumbingleadspermonthl] MOD [subareaonepercentage])

 

[Galaxiom]

The expression you gave me to try was the following. I do not see the "*"

That is because I edited the original post.