I know the Round function exists in MSAccess, but what I need is one that will always round up, and never round down!
Easy enough in Excel, but I can't find one in Access?
I have an expression that sets an AI Buy It Now value that is 125% of AI Value and rounds it, which follows AI Buy It Now: Round(([AI Value]*1.25)/10)*10
I need to add to this expression that I only want this calculation applied if AI Values are greater than 999. But I can't figure out how to add this stipulation to the above expression. I have tried IIF but it is not working.
I create e field with calculation expression and I need to round the results. I insert the following expression:
Round(IIf([A]="N";0;(IIf([B]>0;([B]-[C]-[D]-[E]-[F]+[G]);([H]-[C]-[D]-[E]-[F]+[G]))));2)
I have back the following error message: Could not find field 'Round'.
I do not understand why, because Round is not a field, but a function.
Hi all
l'm running a query that has a field with the following formula;
[Anniv_date]=01/03/2006
[NextEBDay]=01/07/2006
[LastPayToDate]= In this case is 25/11/2005
To_Anniv_wks: IIf([Anniv_date]<[Forms]![DateEB]![NextEBDay],(DateDiff("w",[Forms]![DateEB]![LastPayToDate],[Anniv_date])),0)
The result returns 13. However l know the answer should be 14. Coz when l enter the information in excel l get 13.71.
l've tried using the round function with no luck. Any suggestions would be appreciated.
I am having a small problem when using the Sum function on the footer of my form. I am using the following:
=Sum([UnitQty]*[UnitCost])
UnitCost is a currency and UnitQty is a number
This is working except for one senario. Sometimes the quantity is a decimal. For instance, 27.75 X 0.5 = 13.875. It appears that when the sum is totaling this, it does not round up to 13.88 like I need it to. I also tried the following with no luck:
=Sum([UnitQty]*Round([UnitCost],2))
Any ideas?
Gregg
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 am increasing prices and after increasing I am left with 4 decimal places and am trying to figure out how to run an UPDATE query to round down prices to the nearest 5 cents, examples below:
42.4516 round to $42.45
42.4659 round to $42.45
42.4489 round to $42.40
2.49 round to $2.45
2.46 round to $2.45
2.44 round to $2.40
2.04 round to $2
I am using "Currency" format.
I have is rounding down dates in Access. I seem to be having trouble with the equation I built below:
Months Remaining: IIf(Date()<[Anniversary],Round(DateDiff("m",Date(),[Anniversary],Round(DateDiff("m",Date(),[Anniversary]+12)))))
My goal with this formula is to produce a number of months remaining (rounding down) by subtracting today's date from the anniversary date. The Round function I put in there seems to not work as the result is the same when I remove it.
Another thing I am also trying to accomplish is having the date pushed forward if todays date is greater than the anniversary date. In this instance, I tried adding 12 months to get it back on track. So say the anniversary date is july 4th 2015 and todays date is aug 4th 2015, well thats gonna show negative 1 but if I add 12 it should bring it to 11 months remaining -which would make sense because the anniversary month and day is fixed but the years just get pushed.
I'm trying to get a query to perform a calculation and round the results.
The fields that I am running the calculation and am trying to round are Data Type Number, properties Field size Single and decimal places Auto.
I have tried the built in function described in Allen Browne's site to no avail.
[URL]
I have tried rounding the individual fields and then adding result.
I have also tried rounding the result (as attached).
I have a table of standard Circuit Breaker (CB) sizes. I then calculate a minimum CB size in a query field. I want to use that calculated minimum value to look up the next largest CB size from the table and fill a field in the query with it.
For now, I am going to add a field to the CB size table with the smallest size CB which would be assigned that standard value. I will then use a Dlookup with conditions of greater than "smallest size" and less than "standard size" fields from the CB size table.
I think this will work fine, but there must be a better way.
I am trying to round off times to the nearest half hour. To be clear, I don't want to only round down or only round up. I need the rounding to be to the nearest half hour. I want to do this in the query, not vba. I've attached a picture of the query.
View 3 Replies View RelatedI have a timesheet database, which has the exact start time and exact end time. I want to ignore this if it is five minutes either side of the half hour, and display the exact half hour, but otherwise want to round it UP to the nearest fifteen minutes.
For example:
Exact Start: 09:58:23 Displays as: 10:00:00
Exact Start: 09:42:16 Displays as: 09:45:00
Exact Start: 09:02:08 Displays as: 09:00:00
Exact Start: 09:07:01 Displays as: 09:15:00
Exact Start: 09:23:03 Displays as: 09:30:00
Exact Start: 09:33:00 Displays as: 09:30:00
Exact Start: 09:36:04 Displays as: 09:45:00
I have about ten append queries to group various financial data to one main table.
I have used the round function (iff (Round(Nz([FIN_data]),2)) in the queries to round the original data into 2 decimal places but there is still one or two lines exceeding 2 decimal places.
What is the better approach to have only 2 decimal places for all append data?
I have two tables. Table1 has two monetary value fields (both set with auto decimal places) and table2 has one monetary field. Then I have an append query which appends all data from table1 to table2, except for the monetary fields which are added up and then appended into table2's single monetary field (which also has decimal places set to auto). The formula I use for this is as follows:
Total Value: [Gross Value]+[Adj'mt Value]
Problem is, the total figure appended to table2 is rounded off to the nearest integer, whilst still displaying decimal places (45.00, 201.00 etc). Is there some sort of variable to set in the query to stop it from rounding off the result of this formula?
Any help would be appreciated.
Thanks.
Hi
I have a table which has information the count of students in classrooms around the university and I need to summarize the table by Faculty. Therefore, all I want to do is a count of students for each faculty i.e. Art and Design, Business and Law etc.
The query i put together is: SELECT Count([tbl_Audited Classroom for Week 02].Faculty) AS CountOfFaculty
FROM [tbl_Audited Classroom for Week 02]
HAVING (((Count([tbl_Audited Classroom for Week 02].Faculty))="BAL"));
So when I run the query I get the error message 'Data Type mismatch in criteria expression'. The Faculty field is a text field, so I don't know if that would make a difference.
Can you please help?
thanks
I seem to be having some trouble with my unit price. Its not round up correctly.
I currently have this :
Extended Price: CCur(Nz([Quantity]+[QuantityToFollow],0)*[UnitPrice]*(1-[Discount]))
Which comes to £45.74, but the actually price should be £45.72. Its some how rounding up. I think. My current unit price is £7.62 however Access seems to think it is £7.6241.
I can not have any negitive numbers show up in my field so I need to round up and negatives to "0" ...
View 10 Replies View RelatedI have a database that has raw data input to 2 decimal places. There are multiple queries set up to sum the total cost by facility and box type. When the queries are run, the output is to 4 decimal places. I need to round the cost to 2 decimal places. (Right now, it is formatted to show 2 decimal places, but when you click on the number, there can be up to 4 numbers after the decimal.
I have tried to round the numbers within the queries and I get errors asking for parameters. I've also tried to round the numbers in the append queries where I move the final numbers to a summary table. When I do that, I get an error message that the database can't find the table I selected.
I'm not sure how or where it's best to add the rounding function so that my final output is in the format I need.
I have a query expression that calculates the number of years between 2 dates using DateDiff. Here is the equation as it is:
BudgetedHQPCalc: Round(DateDiff("yyyy",[DateHired],[DateTerminated]),1)
What I would like this to do, is if the difference is, for example, 1.4, I want the number rounded down, if its 1.6 I want it rounded up, and if its 1.5, i want to use alternate rounding (1.5 down 2, 2.5 up to 3).
Is there an easy way to do this?
I am using access 2010. I have a query with an iif statement on a calculated field im trying to round up. ex.
Code:
TotalDiscount: Round(iif([field1]="Piece",[int]-[decr],[field2]*[field3]/[field4],3))
I get an error something like "wrong number of arguments or expression on comma.
I am trying to aggregate IIF functions to give me the total in separate columns (fields) according to the criteria applied however I am getting an error message "You tried to execute a query that does not include the specific expression
'IIF(Tbl_SKG.Value_Grp = 1,sum(Tbl_Advisor_raw.ValuePay),0)'
as part of an aggregate function, and I cannot find why, The query is as follows:
SELECT
Tbl_Advisor_raw.Month,
Sum(Tbl_Advisor_raw.ValuePay) AS ValuePay,
Sum(Tbl_Advisor_raw.Salary) AS Salary,
Sum(Tbl_Advisor_raw.NetRevenue) AS NetRevenue,
IIf(ValuePay>0,(ValuePay/Salary),0) AS pcSpend,
[Code] .....
I need to clean up data from text file which is huge.
I wonder if Access Expression Builder can mirroring "IF function" from Excel Here is what i am trying to do The data consist of multiple customer and multiple date. But the layout only specified customer ID once eg.
CustID: aaaa
Date
01012013
02012013
03012013
CustID: bbbb
01012013
02012013
When exported the file I used Fixed Width command to separate Date Column and Cust ID column.
Below is the result that I am looking for
CustID: aaaa : aaaa
Date : aaaa
01012013 : aaaa
02012013 : aaaa
03012013 : aaaa
CustID: bbbb : bbbb
01012013 : bbbb
02012013 : bbbb
1. Column A is Date
2. Column B is Customer ID
because Customer ID in column B only appear once, I need to create another column to populate that Customer ID whenever the transaction related with that Customer
3. Column C is the column where i tried to populate Customer ID to each date related to that Customer.
If I worked in excel the formula will be --> IF(AND(C5="",B6=""),"",IF(AND(C5="",B6<>""),B6,IF( B6<>"",B6,C5)))but in access i am stucked.
I have a table in Access 2010 and in one field i have multiple records of the same data as in the next field it has unique data for example:
NameColour
CarBlue
CarGreen
CarYellow
BusOrange
BusPurple
BusRed
I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:
NameColour
CarBlue;Green;Yellow
How i would do this.
I have a field in a table that keeps rounding the data entry off. It has identical properties to the field next to it but one rounds and the other does not. I tried deleting the field and creating a new one.
View 5 Replies View RelatedI have a very basic query with the following calculated field in it:
ProjRevNRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentNRC]*[Rev Flow Through],0)
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'm trying to get this query to work:
Code:
SELECT Avg(Round([Final 205].[LastOfSumOfRehLOS],2)) AS AvgInpLOS, Avg(Round([LastOfSumOfWaitLOS],1)) AS AvgWaitTime, Count([Final 205].PHN) AS Observations, [Final 205].LastOfIntensity AS Intensity_Level
FROM [Final 205]
GROUP BY [Final 205].LastOfIntensity;
It doesn't found the result to two decimal places.