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?
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 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.
When using the carriage return how do I format the data so it will export with 2 decimals?
In the example below I need the Estimated Material Cost to export in in format: 500.00 --- NOT 500.
MTLCost: [Estimated Material Cost] & Chr(13) & Chr(10) & [C]
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
what is the best/cleanest way to return the next primary key value which is in the format yyyy-###.These are case files for the current year, ### being 3 digit number incremented by one for each new case.
I could use a function that finds the last one, checks the date, increments by one and returns it, but maybe I could use a public property or a class? I'm keen to use classes more for code maintenance.With a class then: would i create a PrimaryKey variable of the class type/object, so when I need a new key I would just type PrimaryKey.NewKey, for example?
how to change default currency in Access 2013 to a "foreign currency" (i.e. to Indonesia Rupiahs) without changing control panel (Region/Language>currency) - now setup as USA ($US)?
View 3 Replies View RelatedI have always relied on Design View to create my queries, usually with great success, but in so doing, I have not become familiar with SQL view, so can't solve this problem:
I have a very simple query whose only function is to sum the payment amounts received for each contract number:
SELECT Payments.[Contract Number], Sum(Payments.[Payment Amount]) AS [SumOfPayment Amount]
FROM Payments
GROUP BY Payments.[Contract Number];
It is then used in subsequent queries to create an accounts payable report. It works great as long as at least one payment has been received on the contract, but if NO payments have ever been received, the Sum is actually null, so that record doesn't get included. I need a way for this query to return zero or currency zero 0.00 for all contracts with no payments yet, so those contracts will be included and the next query in line will see a zero.
In my search, it seems I need to make use of the IsNull function in my SQL (the check expression would be the sum of payments, the replacement value would be zero) OR I need to include a WHEN or WHERE in my SELECT statement above? I just don't know enough about SQL to make this happen .
I need a query to pick the bones out of sales transactional data like this...
Code:
CustomerName Currency
A Smith GBP
A Smith USD
B Jones GBP
B Jones GBP
T Brown GBP
T Brown AUD
C Wong GBP
C Wong GBP
S Giles USD
S Giles USD
From the above data, only A Smith & T Brown ...have made purchases in more than one (different) currency. How on earth do I trap that with a query? (or will it need a macro?)
If so, the table is called sales & the columns are as per the data above (CustomerName & Currency)...
We have a query that calculates the cost total of three different types of costs between three different parameters. I now need to run a query that prompts the user for the days currency conversion rate to create a new report. I don't want to permanently convert the original fields, it is purely a monthly report where when the report is run is shows both British £ and USD.
View 3 Replies View RelatedI have a main report with 5 sub reports. There is a detail page for each company and a totals page at the end. The record source for each sub report is a Union query (combines the detailed information with the total information.
One of the sub reports displays currency amounts. The detailed reports display the currency correctly: $26,001 (no cents). The totals sub report does not display the currency formatting: 468934
When I run just the totals query the amounts display correctly ($468,934) by using the CCUR(TotalAmount) variable type conversion. The VarType for the amount field in the totals query is 5 (double precision).
When I combine the detail query and totals query into a Union query the detail amounts display correctly but the total amount is missing the formatting.
Here is the union query.
SELECT TblCompany.TblCompanykey, FormatCurrency(ProviderCostsRetrieval([TblCompanykey],1),0) AS TotalCost
FROM TblCompany
ORDER BY TblCompany.TblCompanykey
UNION ALL SELECT 9999 AS TblCompanykey, CCur(Sum(([QryRptProviderCostsDuringPeriod.TotalCost]))) AS TotalCost
FROM QryRptProviderCostsDuringPeriod
GROUP BY 9999;
I need to export a currency field to include the pence but without the decimal place, is this possible?
It also needs to be 11 characters wide.
I have tried a number of formats and can get the decimal point removed but I am not getting the pence displayed.
I am looking up a product based on a unique code in tblProducts...I need to return one of two values
If the value of [Product type] = "multi" then return "multi"
else
If the value of [Product type] = "solo" then return the value of the non empty field in a range of fields
for example
tblProducts
PID (key) | PACKAGE TYPE | RED | YELLOW | BLUE
C13T0714010 | single | <empty>| yellow | <empty>
C13T0754010 | multi | red | yellow | blue
query
C13T0714010 returns "yellow'
C13T0754010 returns "multi'
Hi All,
I am wandering if it possible to check programatically whether a query returns a NULL result?
I am writing a program(VB) that involves an IFF statement where if there are records in the query it does a specific calculation & if there are no records returned in the query it does something else. Is this possible to achieve?
Thanks in advance:D
Cheers,
Karv