Reports :: Rounding A Calculated Field (Nearest 3 Decimals)
Apr 17, 2013
I have a field that sums pounds. I need to convert this to tons (rounded to the nearest 3 decimals), then multiplied by $67.50. This will calculate a fee payment.
This is what I have now:
Code:
=Round(Sum([Hazardous_Waste]/2000*67.5),3)
The total pounds is 2675.
After dividing by 2000, Access generates a number of 1.3375. It rounds 1.3375 to 1.337 which generates an incorrect final total. Not sure how to alter this to round properly.
For some reason I cannot enter decimal values without the number rounding up or down. I've already set it to 'Number' and 'double' and have also tried 'Fixed' set to 2 decimal places. Right now the only way it works is if I set it to 'Text'. However I need to do some calculations with these values eventually.
What's strange is I have another table in the same database that is NOT rounding and is working just fine.
I have been searching the forum for the last hour or so. I'm new to access, vba and macros. So I asking for some help, please be gentle with me. I'm am trying to round up the the nearest twenty.
example. I have a table that contains an estimate take-off f and I want to round the pipe footage to the nearest 20 for purchase amounts and update the table.
Not sure what happened to my original post but here it goes again......I have a calculation that I need to perform in a query. Before I do the calculation however I need to round down numbers to the nearest hundred so a number like 1485.45 would be 1400, something like 543 would be 500 and something like 68 would be 0.In excel this is easy for me to do using the following formula/function:=ROUNDDOWN(C2,1-LEN(INT(C2)))Where cell C2 might contain something like 588 the net result is 500.How can I acheive this in Access (query)? There doesn't appear to be a rounddown function nor a len function in access.Any help is appreciated!M Opine
I have a field that holds a unit price. I can't use currency as the data type because the currency type is different for each client. I have another field that specifies the currency type for each department. Up to now I've been using a double format with a 2 decimal places. We now need to increase the decimal places to 4 but don't necessarily want to see all four places if only 2 are required - 1.5000 against 1.50. However if I make the decimals as auto, if I have 1.50 it rounds to 1.5.
Any other way to give a bit of flexibility to this?
I went into the properties if that field inside the query design and changed it to Standard with 0 decimal places and it worked fine.
But when I based a crosstab query off the query that contained the above calculated field, I cannot seem to get the numbers to format correctly. 1231313.424 is shown instead 1,231,313 and I don't have a line in the Properties window to even change the decimal places. It doesn't recognize when I change the format to Standard. I have tried using Round([ProjRevNRC]) which gets rid of the decimal places as desired but does not show commas.
I'm sure it's a simple part of the Round expression that I am missing but nothing has worked.
I have created a sub form in a form that allows data entry for order details. I have a column that holds order amounts that automatically rounds up or down, and I don't want this. I have examined the back end table properties for this particular field, and the number field is set to general.
I have created an Access 2007 database that uses a form for data entry. While the data is stored on the table exactly as entered, the data entry form rounds up and only displays the whole number.
In the properties of that form field, I have the format as fixed, with decimal places as set to 1 and is visible. However, the number still rounds. What do I need to do differently to make the number display as entered on my data entry form?
The intention was that it would add " (LTSU?)" to the calculated number where it was less than 1 (or not a whole number). Stupidm, me didnt account for fractions greater than 1 (ie 1.566). Just wanted to know how I can identify all 'non-whole' numbers in the above IIF statement.
Is there anyway to turn off the rounding function in calculated fields - I need to divide a currency amount by a whole number to calculate the number of full coin bags that will be needed (bags contain different totals according to the coin value) - a whole number.
Then the result is used to calculate how much loose coin will remain.
But regardless of the field type and/or the format, the number rounds when I reduce the decimal places to zero. I need the result to be the whole number and to calculate as the whole number.
I have a database which gathers and stores the odometer readings of our company vehicles every month. I have built a simple report with columns for Vehicle Number, Employee Number, Employee Name, Month, and Odometer Reading. My boss wants a field for each employee which compares the records for the last two months and displays the difference (i.e. the number of kilometers travelled in that month) /
I have a report that has four fields: Item, Qty, Price and TotalPrice for each line in the detail section. Total Price is calculated by multiplying Qty x Price. The text box name that holds the Total Price for each line is txt_TotalPrice. I want to have a Grand Total in the report footer. I placed a text box in the footer with the following expression: =sum([txt_TotalPrice]). When I run the report Access prompts me for the parameter value of txt_TotalPrice. I've been trying to solve this for quite a while now - but I'm totally baffled.
I have a report and I am trying to Round Up the calculated field SumOfAccrual Amount to 2 decimal places. I am attaching a screenshot of my report and output.
I'm working with a report that totals the number of times a topic is returned from a query. If a topic is not returned at all, i don't want it to show at all. Currently it is showing a blank field for that topic name and blanks in the count as well. Here's the filter i've put in to pull the right data out of my query: =Sum(IIf([Caller Used Resources]="No",1,0))
I have a table of timecards each recording minutes spent on each task for each client and I have summed those minutes by client in a query.I have then in the same query converted each sum of minutes to hrs:mins format using this calculated field:
HrsMins: [SumOfMinTime]60 & Format([SumOfMinTime] Mod 60,":00")
So this turns e.g. 261 minutes into 4:21..Based on that query I have a report showing total hrs:mins spent on each client, e.g.
Smith 4:21 Jones 5:32 James 1:23
Now I want to show a total at the bottom, i.e. in the above example it would be 11:16.So for the control source for a total field on the report I tried:
=Sum([HrsMins])
But when I do that I get an error message: "Data type mismatch in critieria expression"I may be because I am trying to sum calculated fields amounts that are calculated using a function.Or it may be that the format is not being recognised as hours and minutes and thus cannot be added up.way in which I could get a total here in hours and minutes format (hrs:mins).
Basically i have a form where i get info from multiple tables. On the main form itself i have 3 calculated fields for hours where i add all the hours i choose (from a subform) onto the main form.
My issue is i can create a query to come up with all the fields for my report, but how do i get the calculated fields on my main form on the report? Is there a way to print the calculated fields on the main form to a report? or do i have to do the same calculations on the report itself?
I have been trying to figure out why my subreport is only showing sales price for only one of the records on my subform. Everything else works as it should, but it only displays the sales price for the record which is active on the subform.Attached is the database with the subreport called rptProposalItems with the field 'Sales Price' which is experiencing the issue.
I have a form which works good enough. In this form, there is a text box that counts and calculates records from a subform. The name of this text box is "text1" ...
I'm creating a report to check for over- or under-stocked items. The report is working fine, gets all the records etc. except that it thinks that the OnHand field from my inventory query is text or something, at any rate not a number. I have successfully set the format of the field in the query to General Number, but that doesn't seem to have worked. Here's the SQL for the report:
Code: SELECT DISTINCT Signs.SignCode, Signs.SignDescr, Size2.XYdim, qryOnHand.OnHand, Bins.Rack, Bins.Level, Bins.BinNum FROM (Size2 INNER JOIN (Signs INNER JOIN (Items INNER JOIN qryOnHand ON Items.[ItemsID] = qryOnHand.[ItemsID]) ON Signs.[SignID] = Items.[SignID]) ON Size2.[SizeID] = Items.[SizeID]) INNER JOIN (Bins INNER JOIN InventoryDetail ON Bins.[BinID] = InventoryDetail.[BinID]) ON Items.[ItemsID] = InventoryDetail.[ItemsID] WHERE (((qryOnHand.OnHand)<=[Check for signs with fewer than:]));
The calculation works fine; however, I only want to show the whole number (not rounded up; not rounded down). In other words, if the result is 0.9967, then I want 0 to appear.
I cannot figure out how to make this happen. In the properties section I ensured the format was "general number" with 0 decimal places but this didn't change the result. I tried changing the format in the calculated field, again, I wasn't successful.
