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 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 have this expression I created and I want to know how to format it as currency. I know how to do it with a regular field but the option is not available for an expression.
The expression:
Code : Charge: IIf([Rate] Is Not Null,[Rate],"0.00")
I run a MakeTable Query on some data to COunt the number of records and Sum the Value of $$$ in a Currency field.The results produce a Number for the Count, but the SUM of the Balances is returned as text.
To get the SUM I use the expression SumBal: DSum("[Balance]","[XAM_data]").
Why it won't return the SumBal in Currency format?
This is probably the easiest question to answer if you know where to look but I don't.
Working in the UK with Access 2000 I need to set the field in a table to show Dollar $ USD however the only values I have are £ GBP and € as defaults.
Now I thought that I could get this to change if I changed my regional settings but then it occurred to me that if I do that and it changes then surely it would mean that who ever used this database in our global organisation would end up with a currency shown in their own local currency. Whilst this may not seem like a problem it is if you don't realise and quote a figure to someone in your local currency.
i.e. at present the UK £ = nearly $2 USD
So if someone in the UK read the figures and it dispalyed £400,000 and quoted this to a person in the USA when in theory the figure was meant to show $400,000 it could cause all sorts of issues.
Can someone tell me the correct way of choosing $ without it affecting anything else.
Hello,
I would like to format the currency values in a query. There values will either be Euro or Pound. Is it possible to change the currency format "on the fly" in this way in a query?
I would like to have a variable currency format. For example:
If [fldCurrency] = [Euro] Then format [fldAmount] As Euro
If [fldCurrency] = [Pound] or [GBP] Then format [fldAmount] As Sterling
How does one format data as Euro?
Many thanks,
Dj_T
Hi
I have a field on a Form that displays values in Sterling (The Default on the machine) ie with the pound sign before the numbers.
I need to change the sign before the numbers to Dollar at a click of a button. I'm not interested in actually converting the values from Sterling to Dollar but only to change the sign. So I attached the code below to a Button's On Click Event, but I get an error = Expected.
Format([ControlName] , "$#,##.00")
ViRi
I have a calculated field in my query that takes the difference between to incomes. I want to format this field to currency. How can this be done?
View 1 Replies View RelatedHi All,
My Querie:
I have a relational database, that is basically made up of a few tables, I will
simplify for this to two.
TblFieldNames and TblFieldValues
Basically using a form you can fill in a field (TblFieldNames.FieldName) and it
is then appended to TblFieldValues.FieldValue.
On the form the properties of the input text box are changed according to the
record selected in TblFieldNames.
Ie. ListBox with FieldNames from TableFieldNames
ListBox_AfterUpdate sets properties of
TxtValue
The Issue:
I have a Field in TblFieldNames that has a setting of Currency.
(TblFieldNames.FieldType = "Currency")
I have changed the TxtValue.Format = "Currency"
It doesn't:
A. Allow entry as a currency, ie. $ sign and thousand seperators
B. Append the details to the TblFieldValues Table, which it is going across to
into a field that is set as Text (this can't change)
I have tried:
Changing format of input Txt box
Changing Input Mask for inptu Txt Box
"$###,###,###" but doesn't work for me....
"/$###,###,###"
"/$###,###,###!"
I want it to allow me to enter in the Value as a Currency and append the value as a currency
ie.
Key Press = 12345
Field Shows = $12,345
Appends this value as Text?
Key Press = 1234567890
Field Shows = $1,234,567,890
Etc
Any ideas?
add a format to the Sql select for currency.This is just a section of the full code before
Code:
Function RunLWSCA()
Dim cnn As ADODB.Connection
Dim rstUplo As New ADODB.Recordset
Dim rstDiag As New ADODB.Recordset
Dim rstTemp As New ADODB.Recordset
Dim SQL As String
Dim strDiag
[code]...
I am just wondering if there is an advantage to using a currency field type in a table, vs. just using a number field? This is assuming that I am not working directly in the tables themselves and am using forms to display the data.
View 4 Replies View RelatedHi,
I have a query with 3 fields - PaymentAmount and PaymentTotal. The third field is a percentage of the two e.g:
PaymentAmount = 10000
PaymentTotal = 12000
PaymentPercentage = 83.333333333333
In my form I would like a text box to display the two combined i.e. £10.000.00 (83.33%).
At the moment it displays 10000 83.3333333333333. I can use £#,##0.00 to format the current half, but how can I then format the percentage to display 83.33%?
Thanks
I have a Concatenate string of text and currency. When joined together, the currency format disappears. I need to reformat so the string stays together with new format. Below is what I have:
Public Function ConcatAgreementFundsCommitted(ID As String) As String
'/ Purpose: Generate a concatenated string of Tracks for selected line.
On Error GoTo Err_Handler
Dim lngLen As Long 'Length of string.
Dim strOut As String 'Output string to concatenate to.
[code]....
I need 'Funds_Committed' to display as currency.
I have created access database having field currency but it shows currency in $ by default I want to changed it to Rupee. How could it do?
View 1 Replies View RelatedHow do I apply this - can't seem to get it right - just want to control user into putting whatever and two deciimals for pence.
99.99 input mask is halfway there - except I don't want to limit the pounds amount. putting 99999.99 makes entering 12.31 etc a little awkward.
Cheers
I want to store currency amounts but not use the currency format. My DB is set to use all currencies and provide in all currencies.
Do I use the SINGLE data type?
Is there a way to show negative numbers in brackets without selecting the Currency format?
I want to show (75,000.00) and not -75,000.00
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 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 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.