Queries :: Get A Query To Perform A Calculation And Round The Results?
Feb 20, 2015
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).
View Replies
ADVERTISEMENT
Feb 23, 2007
How do I get a field to calculate this formula?
I want a script to find the precise current date and from another field find a number and add it to the current date day to make current date + number = date in the future.
For example
Field: Total Nights Hired = 2
Field: Date due back = 23/02/07 + 2 = 25/02/07
I am sorry if I did not explain this well enough. I am totally new to databases but need help in my corsework. I am designing a video rental system.
Any help will be greatly appreciated.
Andy
View 7 Replies
View Related
Jul 28, 2014
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.
View 6 Replies
View Related
Mar 27, 2014
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.
View 5 Replies
View Related
Sep 15, 2014
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.
View 12 Replies
View Related
Oct 10, 2014
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?
View 2 Replies
View Related
Apr 23, 2014
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.
View 10 Replies
View Related
May 23, 2014
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 Related
Oct 6, 2004
I have a database which is used by operators to input manufacturing data i.e. LotNo, Customer, SalesOrderNo, PartNo, LineNo, ActualQty, Length, ScrapMat, (MatStdCost), StdLineSpeed, SetupTime, RunTime, LostTime and weightperpiece.
We then use this data to produce information like
Standard Run Time: (([ActualQty]*[Lenght])/[StdLineSpeed])/60
Machine Efficiency: ([Standard Run time]/[Runtime])
these work fine if values are inserted. The problem is that when a value is zero it returns a #error value in the field. This is probably caused by dividing by zero which can happen.
A test may occur where there is zero StdLineSpeed etc.
Is there any way I can bet access to return zero in these cases instead of the #error because I wish to do further calculations on totals and averages and I cannot if even one #error occurs.
Any suggestions or help would be appreciated.
Regards
Niall
View 7 Replies
View Related
Aug 6, 2014
I am needing a query to calculate elapsed time in business hours for each record selected (I normally base this on a date range). For the purposes of this query, business hours are defined as Mon-Fri from 7 AM until 9 PM.
So for example:
With a start time of 6:45 AM and an end time of 9 AM, the query would need to return 02:00 (in [h]:mm format).Likewise, with a start time of 7 AM and end time of 9 AM the query would also return 02:00.Is there any way to do this easily? Or at all for that matter? Is it possible to deal with weekends?
View 5 Replies
View Related
Aug 5, 2014
I am trying to set up a calculation between two values to show the percentage difference. In Excel, for example, I would have two values, £905,175 and £891,563, and I would enter =A1-G2)/ABS(A1), which would then return a plus or minus percentage value. how to do this in a query using Access 2010?
View 3 Replies
View Related
Sep 2, 2013
I 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
View 11 Replies
View Related
Mar 26, 2013
I have the following age calculation query:
Age: (Now()-[DOB])365
It works a treat! However, I do not want this to continue to calculate if the record has them as deceased - I want it to stop at their date of death.
I have a tick box that when selected indicates that this record has died, and a field where you can enter date of death.
Is there some way that via clicking this button, or by entering a date of death, I can stop the Age Query from calculating for just that relevant record, not all of them? If so, where to place the necessary VBA, etc?
View 8 Replies
View Related
Apr 13, 2015
I have a database with a Date of Birth field. I have a query with a field that calculates the age from the Date of Birth (DateDiff("yyyy",[Date of Birth],Date())+Int(Format(Date(),"mmdd")
As a criteria in this field I want to be able to select a minimum age, so >=[please input minimum age]
However the results are bizarre - sometimes it gives the right answer, and sometimes not. It seems to have a particular problem with ages above 10, which show up all the time.
View 5 Replies
View Related
May 8, 2013
I created a simple calculation query to add the values of three fields:
Program_Cost, Auditorium_Cost and Millage_Fee.
I followed the steps found here: [URL] ....
But it doesn't work. The query pulls the values for the relevant fields but doesn't actually calculate the total. What am I doing wrong? Here's the query's SQL:
SELECT [Event Information].Event_ID, Sum([Program_Cost]+[Millage_Fee]+[Auditorium_Cost]) AS Total_Cost, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee
FROM [Event Information]
GROUP BY [Event Information].Event_ID, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee;
View 2 Replies
View Related
Apr 4, 2006
Hi there.
Does anyone know how to use an update query to copy some records from one table into another?
I have table1 which has all the contact details and what I would like to do is work out a process for importing records from an excel spreadsheet.
What I have so far is as follows:
1. In access go into the queries tab and select update query
2. Open both table1 and table2
3. Drag the fields to be updated (table1)
4. In Update to select [table2].[table2 column name]
When i run this nothing appears to happen. 0 records are appended.
Does anyone now how to go around this with another solution or should I be using other SQL methods?
All comments much appreciated
Thanks
View 1 Replies
View Related
Nov 21, 2014
I have a query with a DateSerial Calculation field that I would like to filter the query by. The DateSerial calculates the same day of every year (5/31/"YYYY"). When I try to add a criteria sort to this field, I get a data mismatch error. Here is the code: ThirdMay: DateSerial(Year(DateAdd("yyyy",3,[LastDayYear])),5,31).
How do I get only dates due in 2015 to show? I have tried all the standard date criteria to no avail.
View 1 Replies
View Related
May 16, 2013
I need to create a form that using combo boxes selects a product-size-quantity, and then calculates total price.I asume that I create a query to make the calculation from the form, but for the life of me, I can't fathom out how to do it.
View 3 Replies
View Related
Dec 9, 2013
I have played with this problem for 3 days and have come close but not quite solved it. My problem, I have several drivers delivering several orders, the orders are named 101, 102 and so on lets say to 150. Due to locations of the drivers, some deliver more orders then others. I want to be able to create a report that looks like
"Driver #1 101 - 106"
"Driver #2 107 - 110"
Driver 1 delivered 6 orders. Driver #2 delivered 4 orders and so on.
I have tried the 'count" which gives me the number of orders per driver but having trouble figure out the design of the calculation in the query.
View 2 Replies
View Related
Jan 22, 2014
I am trying to calculate the total hobbs time (Ending Hobbs - Starting Hobbs = Total Hobbs) based on a user inputed date range. The query that I created (see attachment) doesn't seem to give me what I'm wanting.
View 14 Replies
View Related
Apr 24, 2013
I have a database that has 2 forms. After submitting the first form, the user should complete the second form within 24 hours. The first form stores the Date/Time the form was submitted. I want to be able to run a query and have a column in the query that is "Time Remaining". In non-technical terms, this column would be: Date/Time form submitted + 24 hours - Current date/time.
View 3 Replies
View Related
May 12, 2013
I want to create a calculation query that uses different equations under certain conditions. Here's specifically what I need:
If the "Cost_Category" field is "Full Price" then the query uses the following calculation:
Total_Cost: Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost]))
If the "Cost_Category" field is "BOCES" then the query uses the following calculation:
Total_Cost: Sum(nz([BOCES_Number_of_Participants])*nz([Cost_Per_Person]))
I have successfully created these two queries individually, but combining them doesn't seem to work. Here's what I wrote:
Total_Cost: IIf([Cost_Category]=Full Price,Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])),
IIf(Cost_Category]=BOCES,Sum(nz([BOCES_Number_of_Participants])*nz([Cost_Per_Person]))
It keeps coming up with errors, saying that I misplaced a comma, parenthesis or quotation. I've tried playing with it, changing the syntax slightly but it doesn't seem to work.
View 2 Replies
View Related
Feb 25, 2014
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?
View 4 Replies
View Related
Sep 22, 2005
In a query I have placed functions that work fine. Now I needed to round a currency number to the nearest $10. ex. 224.49 would be 220.00. I used round(xxxxxxx,-2). this gives me an error. positive 2 works fine. What's the deal? thanks for anyone who can help me. :)
View 3 Replies
View Related
Apr 17, 2006
I track reports in a database. I have a date field [Approval_Date] in the database table. I want to review the reports after they have been approved for 6 months to see if the actions fixed the problem, so I created a query based on the table and used the code "6-Month Review Date: [Approval_Date]+180". This works great except that our meetings are always on Wednesday. So, is there a way to modify my code so that it calculates the 6-Month Review Date as 180 days after the Approval_Date, but then rounds up to the next Wednesday?
I really appreciate the help.
Thanks,
Jim
View 9 Replies
View Related
Aug 11, 2005
Hi,
How can I edit a query in SQL view to run multiple lines of SQL?
For example I can use the GUI to create a query, then in SQL view enter the following:
DELETE
FROM Employees_copy;
which clears the Employees_copy table. Perfect. But what if I want to do this for mulitple tables in the same query eg for a second table Employees_copy_2 as well? I've tried
DELETE
FROM Employees_copy
DELETE
FROM Employees_copy_2;
which gives a syntax error, as does
DELETE
FROM Employees_copy,
DELETE
FROM Employees_copy_2;
any suggestions?
View 2 Replies
View Related
