Rounding Totals (1 Viewer)

[Odessit80]

I have a text box on my form that calculates the total of all purchases. I saw a rounding function in Access, but I need something that I can customize. For example, if the total order is $90, it would round it to $100, if the order is $19, 693 it would round it to $20K. Basically, it would round depending on the total. Is there a function like that in Access or do I need to create the code myself?

Thank you.

 

[Newman]

There is no way that Access can tell how you want to round it.
Is 74.9 rounded as 75, 80 or 100?

You need to code it down...
IIF(Field<=20,20,IIF(Field<=50,50,IIF(Field<=80,80,100)))

 

[Odessit80]

Thanks for the help. Under which event for the text box do I need to code it?

 

[Newman]

This code will be a new field in the query that is used as the source for your form. Give it a good name and put that name as the source of your textbox.

If you use a table directly instead of passing by a query, then this code should be the new source of your textbox. Just replace the word "Field" by the name that was already in the source.

I strongly suggest that you use the query version though.

Good luck!

 

[Odessit80]

Thanks, Newman.

However, there should a more efficient way to do this rather than use a whole bunch of IIF statements. I would have to have an IIF statement for each possible range of the total. For example, I get a total that is $90 and want to round it to the nearest 100. I need an IIF statement for that, then if the total will come to $110, I need to make it to $200. Basically, I would have to write an IIF statement for each case, as far as I understand. And I have to go pretty far, like to $100 k or so. Is there a way to for Access to determine how many digits is the total. Like maybe turning a number to a string, and then return the number of digits. Based on that number I would round to the closest hundred or thousand, depending on the number returned.

 

[Bat17]

I would use a custom function using a "select case" to do this as it will be much easier to maintain

something like

Function RoundItUp(dblValueIn As Double) As Long

Select Case dblValueIn
Case Is <= 100
RoundItUp = 100
Case Is <= 200
RoundItUp = 200
Case Is <= 500
RoundItUp = 500
Case Is <= 1000
RoundItUp = 1000
Case Is <= 10000
RoundItUp = 10000
Case Is <= 100000
RoundItUp = 100000
Case Else
RoundItUp = 100000000
End Select
End Function


HTH

Peter

 

[Odessit80]

Thank you for your reply, Peter.

I am really new to this stuff, but I programmed in other languages. How do you call a function in access?

Thanks!

 

[Bat17]

Just treat it the same as any other function

=RoundItUp([MyField])

Peter

 

[AnnPhil]

I needed to round to the nearest 1000 and found this link to be very helpful!

http://allenbrowne.com/round.html

This is some of what is included on the site:

Round to $1000
The Round() function in Excel accepts negative numbers for the number of decimal places, e.g. Round(123456, -3) rounds to the nearest 1000. Unfortunately, the Access function does not support this.

To round to the nearest $1000, divide by 1000, round, and multiply by 1000. Example:
1000 * Round([Amount] / 1000, 0)

To round down to the lower $1000, divide by 1000, get the integer value, and multiply by 1000. Example:
1000 * Int([Amount] / 1000)

To round up to the higher $1000, divide by 1000, negate before you get the integer value. Example:
-1000 * Int( [Amount] / -1000)

To round towards zero, use Fix() instead of Int().

 

[gemma-the-husky]

out of interest - why round at all? what is the purpose of the rounding?

 

[AnnPhil]

Its what the customer wanted

 

[Dairy Farmer]

Why oh why is mod (supose int comes close), roundup and rounddown not included in Access as basic functions. Works a treat in Excel.

 

[gemma-the-husky]

Why oh why is mod (supose int comes close), roundup and rounddown not included in Access as basic functions. Works a treat in Excel.

eh?


round mod int fix / (integer division, as oppsed to normal \) are all available in access vba.

probably others as well