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 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 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 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.
I'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 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 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 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.
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
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.
I am trying to write a query, it needs to multiply 2 numbers from separate columns, then, I need it to Round the value UP to the nearest 0.25? Does anyone know how to write this formula in a query?
I am having some problems with rounding numbers. I have a DB using forms and i want the user to enter a weight and it rounds to the nearest half KG, so either ?.5 or ?.0 depending on what the number is. I know i can enter a range check in the table validation or say => 5 AND <= 10 but i am stuck on the rounding validation. I think this must be enterd into the text box of the form but as to what the syntax would be i am lost.
I have the following expression in a query. This gets the order due date takes away todays date then goes through a series of calculations to work out the period in terms of a number of months (Assuming they are all 4 weeks, wrong I know)
((((([Blanket_One]![Due Date]-Date())/7))*5)/20)
My problem is fairly simple I think. I need the output from this expression to always round up to the nearest whole number.
Example (1.0009 would round up to 2)
In excel I can use the function Roundup, is there an equivalent in Access?
