429,471 Members | 711 Online
Need help? Post your question and get tips & solutions from a community of 429,471 IT Pros & Developers. It's quick & easy.

# problems rounding up

 P: 44 Hello - I am new at access and would greatly appreciate help. I have a textbox that lists salary. I need my report to show salary rounded up. ex: salary is \$24,500.00. I want report to show 25,000. Is this possible. I have looked up info on function "ceiling" but no luck!! May 10 '07 #1
14 Replies

 Expert 5K+ P: 8,627 Hello - I am new at access and would greatly appreciate help. I have a textbox that lists salary. I need my report to show salary rounded up. ex: salary is \$24,500.00. I want report to show 25,000. Is this possible. I have looked up info on function "ceiling" but no luck!! Here's a little Code Template I through together prior to bedtime. I'm sure you can play around with it and tailor it to your needs. Expand|Select|Wrap|Line Numbers Public Function fRoundSalary(curSalary As Currency) As Long   'Fix will truncate the Decimal portion of curSalary Select Case curSalary   Case 10000 To 99999     'Round to the 1000     fRoundSalary = CLng(Fix(curSalary) / 1000) * 1000   Case 100000 To 999999     'Round to the 10000     fRoundSalary = CLng(Fix(curSalary) / 10000) * 10000   Case Else End Select End Function To Call this Function: Expand|Select|Wrap|Line Numbers Debug.Print Format\$(fRoundSalary(88499), "Currency") OUTPUT: \$88,000.00 Expand|Select|Wrap|Line Numbers Debug.Print Format\$(fRoundSalary(88501), "Currency") OUTPUT: \$89,000.00 Expand|Select|Wrap|Line Numbers Debug.Print Format\$(fRoundSalary(553499), "Currency") OUTPUT: \$550,000.00 Expand|Select|Wrap|Line Numbers Debug.Print Format\$(fRoundSalary(555501), "Currency") OUTPUT: \$560,000.00 May 11 '07 #2

 P: 44 Here's a little Code Template I through together prior to bedtime. I'm sure you can play around with it and tailor it to your needs. Expand|Select|Wrap|Line Numbers Public Function fRoundSalary(curSalary As Currency) As Long   'Fix will truncate the Decimal portion of curSalary Select Case curSalary   Case 10000 To 99999     'Round to the 1000     fRoundSalary = CLng(Fix(curSalary) / 1000) * 1000   Case 100000 To 999999     'Round to the 10000     fRoundSalary = CLng(Fix(curSalary) / 10000) * 10000   Case Else End Select End Function To Call this Function: Expand|Select|Wrap|Line Numbers Debug.Print Format\$(fRoundSalary(88499), "Currency") OUTPUT: \$88,000.00 Expand|Select|Wrap|Line Numbers Debug.Print Format\$(fRoundSalary(88501), "Currency") OUTPUT: \$89,000.00 Expand|Select|Wrap|Line Numbers Debug.Print Format\$(fRoundSalary(553499), "Currency") OUTPUT: \$550,000.00 Expand|Select|Wrap|Line Numbers Debug.Print Format\$(fRoundSalary(555501), "Currency") OUTPUT: \$560,000.00 Thanks for the feed back. Can you please tell me where i have to enter the codes. is it in the control that is supposed to display the output? Properties then code builder? I am very new to this.... May 13 '07 #3

 Expert 5K+ P: 8,627 Thanks for the feed back. Can you please tell me where i have to enter the codes. is it in the control that is supposed to display the output? Properties then code builder? I am very new to this.... Enter this code in the Default Value row of of the Control that is suppopsed to display the results of the calculation. [txtTest] is the Field containing the value to be passsed to the Function Expand|Select|Wrap|Line Numbers =fRoundSalary(Me![txtTest]) May 13 '07 #4

 P: 44 Thanks, but I get #Name? in the Control Box. May 14 '07 #5

 Expert 5K+ P: 8,627 Thanks, but I get #Name? in the Control Box. You must substitute your Field name in place of txtTest. May 14 '07 #6

 P: 44 You must substitute your Field name in place of txtTest. I have =fRoundSalary([Me]![Salary]). It returns #Name? :{ May 14 '07 #7

 Expert 100+ P: 1,356 You need to place the first portion of aDezii's code in the VBA window (the module for that form or even a new module). Once you have done that it should work. Expand|Select|Wrap|Line Numbers Public Function fRoundSalary(curSalary As Currency) As Long   'Fix will truncate the Decimal portion of curSalary Select Case curSalary   Case 10000 To 99999     'Round to the 1000     fRoundSalary = CLng(Fix(curSalary) / 1000) * 1000   Case 100000 To 999999     'Round to the 10000     fRoundSalary = CLng(Fix(curSalary) / 10000) * 10000   Case Else End Select End Function May 14 '07 #8

 P: 44 You need to place the first portion of aDezii's code in the VBA window (the module for that form or even a new module). Once you have done that it should work. Expand|Select|Wrap|Line Numbers Public Function fRoundSalary(curSalary As Currency) As Long   'Fix will truncate the Decimal portion of curSalary Select Case curSalary   Case 10000 To 99999     'Round to the 1000     fRoundSalary = CLng(Fix(curSalary) / 1000) * 1000   Case 100000 To 999999     'Round to the 10000     fRoundSalary = CLng(Fix(curSalary) / 10000) * 10000   Case Else End Select End Function I'm sorry I probably sound like an idiot to you guys! I placed the first portion of dezii's code in the form module, but it still doesn't work....:{{ May 14 '07 #9

 Expert 100+ P: 1,356 I'm sorry I probably sound like an idiot to you guys! I placed the first portion of dezii's code in the form module, but it still doesn't work....:{{ Interesting approach one I haven't used in a long time, but it seems to work only when I removed Me from the default value. You still might need to change up the function depending on the value range you are looking for. Expand|Select|Wrap|Line Numbers =fRoundSalary([Salary]) May 15 '07 #10

 Expert 5K+ P: 8,627 I'm sorry I probably sound like an idiot to you guys! I placed the first portion of dezii's code in the form module, but it still doesn't work....:{{ Place it in a Standard Code Module so it is accessible from anywhere within the Application. A Function really should not be declared Publically in a Form Module, it actually acts as a Method of the Form. May 15 '07 #11

 P: 44 Place it in a Standard Code Module so it is accessible from anywhere within the Application. A Function really should not be declared Publically in a Form Module, it actually acts as a Method of the Form. I am still having trouble...:{ Field name that contains the value is Salary. Field name that should have the answer to the calculation is LifeSalary. I am writing the codes in the modules exactly like i see them in your replies. Please help. May 21 '07 #12

 Expert 100+ P: 1,356 I am still having trouble...:{ Field name that contains the value is Salary. Field name that should have the answer to the calculation is LifeSalary. I am writing the codes in the modules exactly like i see them in your replies. Please help. What exactly are you having trouble with? If you enter a default value "=fRoundSalary([Salary])" in properties for the Lifesalary control then if the Salary Field will contain the results. There are many approaches depending on your circumstances. I can see where the default value may not be sufficient such as if you enter the info into salary control then you need to update the LifeSalary control as well. In that case add an on update event to the Salary field and add the following to that event. Expand|Select|Wrap|Line Numbers Me!LifeSalary=fRoundSalary([Salary]) Please provide any details relevant to your problem otherwise we can sit here and guess all day. May 21 '07 #13

 Expert 5K+ P: 8,627 I am still having trouble...:{ Field name that contains the value is Salary. Field name that should have the answer to the calculation is LifeSalary. I am writing the codes in the modules exactly like i see them in your replies. Please help. Make sure that the Procedure exists in a Standard Code Module. Expand|Select|Wrap|Line Numbers Public Function fRoundSalary(curSalary As Currency) As Long   'Fix will truncate the Decimal portion of curSalary Select Case curSalary   Case 10000 To 99999     'Round to the 1000     fRoundSalary = CLng(Fix(curSalary) / 1000) * 1000   Case 100000 To 999999     'Round to the 10000     fRoundSalary = CLng(Fix(curSalary) / 10000) * 10000   Case Else End Select End Function Make sure that your Text Box 'Control Names' are LifeSalary and Salary. Enter =fRoundSalary(Me![Salary]) as the Default Value for the LifeSalary Text Box. Allow for the Case Else possibility within the Function (<10000 or >999999). If this still doesn't work, I'm honestly lost for an answer. May 21 '07 #14

 P: 44 Make sure that the Procedure exists in a Standard Code Module. Expand|Select|Wrap|Line Numbers Public Function fRoundSalary(curSalary As Currency) As Long   'Fix will truncate the Decimal portion of curSalary Select Case curSalary   Case 10000 To 99999     'Round to the 1000     fRoundSalary = CLng(Fix(curSalary) / 1000) * 1000   Case 100000 To 999999     'Round to the 10000     fRoundSalary = CLng(Fix(curSalary) / 10000) * 10000   Case Else End Select End Function Make sure that your Text Box 'Control Names' are LifeSalary and Salary. Enter =fRoundSalary(Me![Salary]) as the Default Value for the LifeSalary Text Box. Allow for the Case Else possibility within the Function (<10000 or >999999). If this still doesn't work, I'm honestly lost for an answer. Thank you! The text box Life Salary is calculating now, but how can i fix it to round up. On a salary of \$29,500, it needs to calculate \$30,000. May 21 '07 #15