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!!
14  1975  
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. - 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: - Debug.Print Format$(fRoundSalary(88499), "Currency")
- OUTPUT: $88,000.00
- Debug.Print Format$(fRoundSalary(88501), "Currency")
- OUTPUT: $89,000.00
- Debug.Print Format$(fRoundSalary(553499), "Currency")
- OUTPUT: $550,000.00
- Debug.Print Format$(fRoundSalary(555501), "Currency")
- OUTPUT: $560,000.00
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.
- 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:
- Debug.Print Format$(fRoundSalary(88499), "Currency")
- OUTPUT: $88,000.00
- Debug.Print Format$(fRoundSalary(88501), "Currency")
- OUTPUT: $89,000.00
- Debug.Print Format$(fRoundSalary(553499), "Currency")
- OUTPUT: $550,000.00
- 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....
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 - =fRoundSalary(Me![txtTest])
Thanks, but I get #Name? in the Control Box.
Thanks, but I get #Name? in the Control Box.
You must substitute your Field name in place of txtTest.
You must substitute your Field name in place of txtTest.
I have =fRoundSalary([Me]![Salary]). It returns #Name? :{
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. - 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
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.
- 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....:{{
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.
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.
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.
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. - Me!LifeSalary=fRoundSalary([Salary])
Please provide any details relevant to your problem otherwise we can sit here and guess all day.
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.
- 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.
- Make sure that the Procedure exists in a Standard Code Module.
- 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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: spebola |
last post by:
I am using vb.net 2003 professional and I get the following results
when using the round method:
dim Amount as decimal = 180.255
Amount = Amount.Round(Amount, 2)
Amount now contains 180.25. ...
|
by: Zorpiedoman |
last post by:
Howcome:
Dim D as decimal = .5D
msgbox d.Round(D, 0)
this returns "0"
Now when I went to school .5 rounds UP to 1 not DOWN to zero?????!!!
Documentation says this, but what the heck are...
|
by: Jiri Nemec |
last post by:
Hello all,
I have got one table with rounding values, table contains
prices and round types.
id price_from price_to rounding
1 0 1500 0.1
2 1500 ...
|
by: G Patel |
last post by:
Hello,
I'm having trouble with floating point problems.
I'm trying to write a function that calculates the distance between two
cartesian points (integer coordinates). I have the function...
|
by: cj |
last post by:
Lets assume all calculations are done with decimal data types so things
are as precise as possible.
When it comes to the final rounding to cut a check to pay dividends for
example in VB rounding...
|
by: AdINo |
last post by:
I'm having problems with rounding numbers
pls help me out
this is the program instruction that i wrote
x = 420.30 * 0.15
y = 420.30 - x
i print the y out in .2lf format
...
|
by: jdrott1 |
last post by:
i'm trying to round my currency string to end in 9. it's for a
pricing application.
this is the function i'm using to get the item in currency:
FormatCurrency(BoxCost, , , , TriState.True)
if...
|
by: =?Utf-8?B?UmVuZQ==?= |
last post by:
Hello everyone
I have a problem with Math.Round, it´s ocurring some strange:
Math.Round(12.985) = 12.98, it´s wrong. It should be: 12.99
Why?? What is the problem?
Help ME !!!!
|
by: md |
last post by:
Hi
Does any body know, how to round a double value with a specific number
of digits after the decimal points?
A function like this:
RoundMyDouble (double &value, short numberOfPrecisions)
...
|
by: jacob navia |
last post by:
Hi
"How can I round a number to x decimal places" ?
This question keeps appearing. I would propose the following
solution
#include <float.h>
#include <math.h>
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
