Currency Field Formatting - Simple But Annoying Issue!
Jan 10, 2007
Hi All,
This is my first post so can I just say how great and useful this forum has been for me since I joined! I have found pretty much everything I have wanted without having to ask, so thank you everyone!
I am only beginning with Access at the moment, as I try to implement some improvements to the way my company works!
I have one really simple, but annoying issue at the moment and I cannot find the answer anywhere!
I have a currency filed in my table. I want to format it in USD, but USD is not available in the format options, just £ and €. I cannot see where the hell I get the USD $ format from?
I know this is probably well simple and I am being a plum!
I have a parent form and a sub-form. On the parent form is a date field [JOINED] and on the sub-form there is a date field [DATE PAID] and a field [BQ JOINING FEE] with a default format of currency.
I created a macro to set the value of [BQ JOINING FEE] to 0 if ([DATE PAID]>[JOINED]+60). It sets the value to 0 as required but I am unable to get it to format the result as currency.
I have a calculated field in a query that generates a result that i need to display in forms/reports with the $ sign but zero decimal places (ie; rounded to the nearest $ value, $5).
If i use the sql format currency function (format (n, "currency",0) it always displays the $ sign but with two decimal places...
In the form/report text box property i set the format to currency, with the decimal places set to 0, but it still displays two decimal places (this property setting seems to make no difference other than displaying the $ sign). Is there something i am missing regarding the way access formatting works?
A friend of mine has recently created a simple database which holds details of childrens story books. It logs the name of the Author, the storyline, its character etc in various tables...
He's currently creating some simple queries for his database. However, there is one query which neither he nor I can figure out, I'll try to explain to the best of my ability.
Basically, the 'story line' field in his database explains the basic plot of the book, some of the plots have the words "son", "dad", "father" and "boy" in them.
Now, he wants to create a simple query, which looks up and displays data which contains the keywords "dad", "father", "son" etc in them. So far he's tried...
Like "*Son*" Or Like "*Father*" etc...
This worked, briefly. The query was bringing up storylines with both contained the words "son" and "father" , which is correct. However, the query was also bringing up story lines with the word "song" in them, or any word which remotely related to one of the keywords, but didn't contain the exact criteria in the query (son, father etc).
In basic terms, how do you create a query which only brings up words that match the EXACT query criteria? In this case, 'Son' or 'Father' or 'Dad' etc? Rather than words which only briefly relate to the criteria, such as 'Song' or 'Fat' etc?
Thanks for your cooperation, feedback and examples would be highly appreciated.
I have some Conditional Formatting of a Currency Field in an Entry Form. The cell is conditionally formatted with a yellow background when the value is zero.
I now wish to add an additional condition to the expression such that even if the value is zero it will not be formatted if a specific check box is TRUE.
I have tried modifying the expression such that it reads;
[currency field]=0 AND [check box]<>TRUE.
It correctly turns off the conditional formatting if the check box is ticked, but does not turn it on again when the check box is unticked.
I have an unbound text box (txtInstAmt) that needs to be displayed from a table (tblLoan.fldInstAmt). Both the fields in the table as well as the text box have been set to Currency.
However, my code doesn't seem to work.
I get an error msg saying that the value isn't valid for the field when I set the decimal places to 2 in both the table and the form. When the decimal places are set to Auto, I get a Data Type Mismatch.
Can someone please tell me what's wrong with this code?
Code:Private Sub cboLoanSN_AfterUpdate()Dim DAOdb As DatabaseDim DAOrs As DAO.RecordsetSet DAOdb = CurrentDb()Set DAOrs = DAOdb.OpenRecordset("Select * from tblLoan where fldLoanSN='" & Me.cboLoanSN & "'")With DAOrsMe.txtInstAmt = .Fields("fldInstAmt")End WithMe.cboDateRepay.RequeryEnd Sub
Hi everyone, My query concerns Currency in that i have declared some variables as Currency but when i try to output them they appear only as if they are formatted as numbers i.e. £0.50 as a currency variable when displayed in a MSgbox displays as 0.5 not a big problem you might think but i am trying to output these numbers to a retail printer as part of an Epos application. i can add a £ sign to the beginnning but adding missing zero's to the end is proving a little more tricky. I have cheked that the international settings on the PC are set to English, £, 2 decimal places ETC Thanx
I need to have a textbox formatted to have 2 decimals. If I take general number and set the decimal to 2 it will display numbers like 89.1. I would need it to be 89.10. I would like not to use the currency format as it puts the $ symbol in the textbox as well. So basically same formatting as currency (because it's a currency I am working with) but without the symbol.
I 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;
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)?
I have a currency field that is not saving pennies. (cents if you're Americian i suppose) :confused: if i put in pence it rounds it to the nearest pound.
I have tried setting the decimal places to "2", but that makes no difference :mad: :confused:
the table is called invoice
this also happens with the invoice form :mad: (i suspect for the same reason)
The database can be downloaded from: http://www.tapestriesdirect.com/Garage.mdb (996KB) or if you can unzip (who can't?!?) http://www.tapestriesdirect.com/Garage.zip (86.1KB)
I have a field which is formatted as currency. When a button is clicked, it shows the value of the field. Because it is a currency field, if you enter "10", it is immedatly changed to "£10.00", although when i use txtAmount.Value, it gives me the "10" value. This is especally a problem if pennies are used - if someone enters "15.10", the .Value reterned is "15.1", which makes it seem like "15.01" has been entered. How can I get the currency-type value form the field?Thanks for any help.
I am importing a spreadsheet from Excel - the problem is that one of the columns in excel has a currency and text format (ex. $123,000 in B5 and 3M in B6). I know in Excel you can have both formats in one column since each cell can be formatted individually - how do I replicate this in Access?
If CIS = False Then CIS_Payment = "£0.00" Else CIS_Payment = "£20.00" End If
which works ok but what i want to do but do not know the correct way/syntax is:
If CIS = False Then CIS_Payment = "£0.00" Else CIS_Payment = "Labour_Total*18/100" End If
Where CIS_Payment and Labour_Total are currency fields, and CIS is a checkbox updated using an update query with the same info as detailed in another table (Couldnt get the conditions to work referencing the checkbox in the other table, so duplicated the box in both tables and used a query to sync)
Basically i have a form which logs error corrections. The users enter a 'date received'. My user has now asked to have a date due to automatically be calculated by adding six days. I have done this with no problem:
=DateAdd("y", +6, [date received])
My form is not unbound, this date due field is the only unbound field i have. This date does not need to be saved in the table, just to appear when the user opens the form, which it does.
The problem which i am having is that i have a report which runs of a query, printing out the error correction, when the user types in the auto number required. The records added after i put the date due field, will not print out and when i run the query and type in the auto number of these records they do not appear...yet they are stored in the table. Obviously this is due to the 'date due' field, but im not sure how.
I really don't understand what is wrong. Could someone please give me an insight into what maybe wrong or tell me what to search for in the forum, because the searches i have tried have not come up with anything.
Hi There, I know this subject has been beaten to death, but I have searched the forum and cannot find a post relating to my particular problem. I have three cascading combos on a subform that work fine and dandy when I open the subform on it's own, but when I open the main form that the subform is embedded into, I get the following message [IMG]cascade[/IMG] I know I am missing something simple. Any advice would be most welcome.
I would like to know is there a way to create a mask on a form for a currency field? I don't want a user to be able to enter in like 125.145. I just want to make it so that people can only type in 125.14. Or how can I write VB code to give a warning when a user enter in more than two decimal places.
I would like to know is there a way to create a mask on a form for a currency field? I don't want a user to be able to enter in like 125.145. I just want to make it so that people can only type in 125.14. Or how can I write VB code to give a warning when a user enter in more than two decimal places.
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")
