I have values for example like, 0.03, 1.14, 1.28 I'd like to round them to the nearest tenth. So resulting values will be as follows:
0.03 = 0.00
1.14 = 1.10
1.28 = 1.30
How do i achieve this? What's the formula?
How can a I round to the nearest 10 cents
e.g
10.04 = 10.00
10.06 = 10.10
10.28 = 10.30 ........
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 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 a table that archives hour meter readings for my equipment. You sharp folks here helped me set it up and it works great. Thanks! I am at the point now where I want to do some studies and calculations on this data. The first project I’m working on is equipment utilization over a period of time. I have all my calculations figured out, all I need is to pull the proper data out and run it through. And that’s where I’m a little stuck.
My table is just three fields: tblHourMeterArchive = eqnum, readingdate, meterreading. I want to be able to input a StartDate and EndDate and have the query choose the archive date that is nearest to the input date and the meter reading on those dates. For example, I have meterreading dates of 1/9/05, 1/30/05, 6/5/05, and 6/19/05. I input 1/18/05 as the StartDate and 6/16/05 for the EndDate, the query should return the archive dates of 1/9/05 and 6/19/05. How would I go about doing this?
I’ve done some research on “nearest date”, but most of the information has been about doing it in VB and SQL. Can this be done without going into coding? I’m still very much a novice when it comes to VB code. Ultimately, I would like to have this work with a form and calendar controls. I’ve been studying some of the examples and suggestions for the calendars, but I’m not really sure how I would go about setting up a query in code and linking it all together.
As always, any help and/or links to information you could provide would be greatly appreciated.
I have got a formula in a Query as follows:
Suggested Qty: [StockQty]/[Numerator]
How do I round this down to the nearest 1?
(i.e 7.2 = 7 or 9.9 = 9)
Any help would be greatly received.
Regards
Carly
Hi, I am quite new to access, and have the following problem:-
I have a value that is returned in a report of say 17.93, i would like to round this number down to 17, similarly 9.13 should read 0
Can someone help please? Many thanks in advance
:)
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.
ie.
Item QTY
Pipe 534.08
Pipe 405.5
Pipe 28.83
Pipe 1381
Item QTY
Pipe 540
Pipe 420
Pipe 40
Pipe 1400
Another problem I'm not sure how to fix is, some of the qty totals are two decimals and others are nine. Maybe not an issue but thought it might be.
So if anyone is compelled to help a greenie out it would be much appreciated.
Thanks
I need to find the nearest dates between two tables.
Table1 contains dates 11/1/2006 to 11/30/2006
Table2 contains only two dates:
11/5/2006
11/19/2006
I want Query1 to produce the nearest date in Table2 beside each of the dates in Table 1.
Example:
Date Nearest Date
11/1/2006 11/5/2006
11/13/2006 11/19/2006
etc.
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
View 3 Replies View RelatedI'm trying to create a query that will return the closest matches to what is input. However the closest i have gotten to this working correctly is by using the following criteria.
Like Left([Forms]![TESTTESTTEST]![Text2],1) & "*"
Now obviously this isn't a great solution as it just produces results that match the first number but i need it to display results closest to the number entered into text2.
Basically i am doing this as a designer can search for a gap on a machine where there new project can fit on causing the least amount of disruption. So by entering in the size of there project the query will display jobs of a similar size that are already planned on the machine.
I need to do a report that takes data from many different tables (like 10).
To do this I have a big query with many LEFT JOIN to link all the tables together. I have made this query last year and it worked well and efficiently until now.
But I have to add something there, and the problem comes with it :
One of the tables is a currency exchange rate table, with the exchange rate of USD to 50 different other currencies and for each day since Jan 1st 2011.
But in this table there are some dates when a currency does not have an exchange rate (example : it was a holiday in that country, so there is no official exchange rate with this currency on that day).
However, I need to have rates even if they are not present in the table.
I need to get the nearest rate, for example if the rate of EUR on 31st December is not available, I can take the one of the 30th, or 29th or January 1st...
Ideally I would like to do the following :
Add a new LEFT JOIN to my already big query that would look like :
LEFT JOIN CurrencyExchangeRate ON ABS(RateDate - Instrument.Date) < 3
But to take only the closest rate.
I tried with a subquery :
LEFT JOIN (SELECT TOP 1 * FROM CurrencyExchangeRate WHERE ForeignCurrency = Instrument.Currency1 AND ABS(RateDate - Instrument.Date) < 3) AS MRate ON MRate.ForeignCurrency = Instrument.Currency1)
But the subquery does not accept Instrument.Currency1 because it is not part of the table CurrencyExchangeRate
I have a solution that should work, it is to search for this rate in a subquery in the select clause instead of doing a left join. But the tables are quite big (hundreds of thousands of entries) and my work machine is not really 'fast' so it would take hours and hours (when I tried I stopped after 3 hours).
I know there is the solution to make a macro to "fill" the missing rates with the rate of the previous day... But I would prefer not to go to this extent.
I would like to add fields from one table to another based on matching datetime codes.
Most data are in the table 'metadata' (information on sound recordings), and i want to add lat long fields from a large table of GPS points 'gpsData'. Each record in the metadata table has start and end datetime fields - i want to a add lat and long that matches the start time and a lat and long that matches the end time (so four fields to metadata).
My approach has been to try this with two separate select queries (i.e. one for adding the start locations and one for adding the end locations), with the intention of then combining these two outputs in a single table.
When i try a simple select query though I can see that:
a) there are no matching time codes for some records, because although the hh:mm:ss look the same there are differences in the lesser decimal places of the datetime serial numbers. I have tried using the round function on the datetime fields of both tables but this doesn't fix it (I don't understand why not).
b) there are some duplicates because there are sometimes more than one GPS point for each second, and the metadata times are given to second precision (so some metadata records get two entries in the query result).
How can I have the query select the gpsData record with the nearest datetime stamp to that in the metadata table's start time field? The respective field names are UTC and StartUTC. Can i do the same for the end times simultaneously or should i do this separately?
I have Territory Mgmt database. Sometimes when I get a new lead I need to quickly find other leads in the database that are closest to it.
I am currently not tracking latitude/longitude etc. Do I need to? Is there a way to set certain location marks by address?
If so, once I put in the latitudel/longitude how do I create a search to find the nearest?
What sum can I use to round down on a report
View 1 Replies View RelatedI 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.
I have to fields in military time. I need to calculate and have the result in decimal format to the nearest .25 minutes.
View 1 Replies View RelatedHi,
I am running a query that has this function
MyRoundedValue: Round([Bonus Total],2)
I am trying to round up the value in Bonus Total.
Currently the result shows 5140.602 and I want it to round up and show 5140.61
Is this possible? For the life of me I cannot figure it out..
Thanks
Fen How
Hi,
Need a little help with rounding up. In A2K I have a form with a textbox that displays a security deposit. Security deposit is calculated by rounding the payment to the next $25 increment, hence a payment of 324.53 should have a security deposity of $325.00, but a payment of $325.01 should have a security deposit of $350.00. The code I am using for a datasource for txtSecurity deposit usually works correctly, but for this payment ($324.53) it rounds to $350.00. Here is the code..
=(([txtPayment]25)*25+[txtSecDepRndTo])
txtSecDepRndTo holds the $25 incremental value.
Incidentally, if the payment is $324.49 my code rounds the Security Deposit to $325.00 as it should.
Any help would be greatly appreciated.
I have main form call frmemployee and subform call frmlcourse subform the main form hold's employee information e.g. first name addresss the sub form hold's all the training courses that the employee has been on
I have a text box called txtnextcourse due to display when the next course due date is
Code:
=DMin("Expirydate","tblcourses","[ID]=" & [empID])
this works perfect. I have check box on my subform called ex course if this is true. I don't want the dmin to count that record I have tried
Code:
=DMin("Expirydate","tblcourses","[ID]=" & [empID]) [forms]![frmlcourse subform]![ex course]=false
I have a field within a table entitled "Name Display" This displays names in the format:
Surname,forename
I have used the following:
Full Name: Right([Name_Display],Len([Name_Display])-InStr([Name_Display]," ")) & " " & Left([Name_Display],InStr([Name_Display]," ")-1)
To rearrange so format is displayed as forename,surname
However my output with some fields appears as:
SARAH WAILES, with spaces inbetween.
Can anyone suggest a way of removing these spaces?
Thanks
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 RelatedHi, I have this query and I would like to have the avg display with only 2 decimal points. This is my SQL and I think I have to use this code but I'm not sure.
FORMAT(CountOfStudent Attended,'.00')
If I ad this after the SELECT statement my query will not work. What am I doing wrong?
Thanks!
~D
This works
SELECT [Attendance for Avg].CRN, Avg([Attendance for Avg].[CountOfStudent Attended])
AS [AvgOfCountOfStudent Attended]
FROM [Attendance for Avg]
GROUP BY [Attendance for Avg].CRN;
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