By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,471 Members | 711 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
14 Replies


ADezii
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
  1. Public Function fRoundSalary(curSalary As Currency) As Long
  2.  
  3. 'Fix will truncate the Decimal portion of curSalary
  4. Select Case curSalary
  5.   Case 10000 To 99999
  6.     'Round to the 1000
  7.     fRoundSalary = CLng(Fix(curSalary) / 1000) * 1000
  8.   Case 100000 To 999999
  9.     'Round to the 10000
  10.     fRoundSalary = CLng(Fix(curSalary) / 10000) * 10000
  11.   Case Else
  12. End Select
  13. End Function
To Call this Function:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Format$(fRoundSalary(88499), "Currency")
  2. OUTPUT: $88,000.00
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Format$(fRoundSalary(88501), "Currency")
  2. OUTPUT: $89,000.00
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Format$(fRoundSalary(553499), "Currency")
  2. OUTPUT: $550,000.00
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Format$(fRoundSalary(555501), "Currency")
  2. 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
  1. Public Function fRoundSalary(curSalary As Currency) As Long
  2.  
  3. 'Fix will truncate the Decimal portion of curSalary
  4. Select Case curSalary
  5.   Case 10000 To 99999
  6.     'Round to the 1000
  7.     fRoundSalary = CLng(Fix(curSalary) / 1000) * 1000
  8.   Case 100000 To 999999
  9.     'Round to the 10000
  10.     fRoundSalary = CLng(Fix(curSalary) / 10000) * 10000
  11.   Case Else
  12. End Select
  13. End Function
To Call this Function:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Format$(fRoundSalary(88499), "Currency")
  2. OUTPUT: $88,000.00
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Format$(fRoundSalary(88501), "Currency")
  2. OUTPUT: $89,000.00
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Format$(fRoundSalary(553499), "Currency")
  2. OUTPUT: $550,000.00
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Format$(fRoundSalary(555501), "Currency")
  2. 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

ADezii
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
  1. =fRoundSalary(Me![txtTest])
May 13 '07 #4

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

ADezii
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

Denburt
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
  1. Public Function fRoundSalary(curSalary As Currency) As Long
  2.  
  3. 'Fix will truncate the Decimal portion of curSalary
  4. Select Case curSalary
  5.   Case 10000 To 99999
  6.     'Round to the 1000
  7.     fRoundSalary = CLng(Fix(curSalary) / 1000) * 1000
  8.   Case 100000 To 999999
  9.     'Round to the 10000
  10.     fRoundSalary = CLng(Fix(curSalary) / 10000) * 10000
  11.   Case Else
  12. End Select
  13. 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
  1. Public Function fRoundSalary(curSalary As Currency) As Long
  2.  
  3. 'Fix will truncate the Decimal portion of curSalary
  4. Select Case curSalary
  5.   Case 10000 To 99999
  6.     'Round to the 1000
  7.     fRoundSalary = CLng(Fix(curSalary) / 1000) * 1000
  8.   Case 100000 To 999999
  9.     'Round to the 10000
  10.     fRoundSalary = CLng(Fix(curSalary) / 10000) * 10000
  11.   Case Else
  12. End Select
  13. 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

Denburt
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
  1. =fRoundSalary([Salary])
May 15 '07 #10

ADezii
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

Denburt
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
  1. 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

ADezii
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.
  1. Make sure that the Procedure exists in a Standard Code Module.
    Expand|Select|Wrap|Line Numbers
    1. Public Function fRoundSalary(curSalary As Currency) As Long
    2.  
    3. 'Fix will truncate the Decimal portion of curSalary
    4. Select Case curSalary
    5.   Case 10000 To 99999
    6.     'Round to the 1000
    7.     fRoundSalary = CLng(Fix(curSalary) / 1000) * 1000
    8.   Case 100000 To 999999
    9.     'Round to the 10000
    10.     fRoundSalary = CLng(Fix(curSalary) / 10000) * 10000
    11.   Case Else
    12. End Select
    13. End Function
  2. Make sure that your Text Box 'Control Names' are LifeSalary and Salary.
  3. Enter =fRoundSalary(Me![Salary]) as the Default Value for the LifeSalary Text Box.
  4. Allow for the Case Else possibility within the Function (<10000 or >999999).
  5. If this still doesn't work, I'm honestly lost for an answer.
May 21 '07 #14

P: 44
  1. Make sure that the Procedure exists in a Standard Code Module.
    Expand|Select|Wrap|Line Numbers
    1. Public Function fRoundSalary(curSalary As Currency) As Long
    2.  
    3. 'Fix will truncate the Decimal portion of curSalary
    4. Select Case curSalary
    5.   Case 10000 To 99999
    6.     'Round to the 1000
    7.     fRoundSalary = CLng(Fix(curSalary) / 1000) * 1000
    8.   Case 100000 To 999999
    9.     'Round to the 10000
    10.     fRoundSalary = CLng(Fix(curSalary) / 10000) * 10000
    11.   Case Else
    12. End Select
    13. End Function
  2. Make sure that your Text Box 'Control Names' are LifeSalary and Salary.
  3. Enter =fRoundSalary(Me![Salary]) as the Default Value for the LifeSalary Text Box.
  4. Allow for the Case Else possibility within the Function (<10000 or >999999).
  5. 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

Post your reply

Sign in to post your reply or Sign up for a free account.