Full Version: Format listbox column to display as currency
UtterAccess Forums > Microsoft® Access > Access Forms
mrmagoo_83
I have a form with a listbox which pulls data from a table via an SQL command. There are 10 columns in the listbox, the last of which contains data that in the table is currency, however, in the listbox it simply shows up as simple numbers. How can I format it so that the column data shows up as a currency format? It just needs to be that one column.
pere_de_chipstick
Hi
I am not sure that you can format a list box column to display a currency.
HAs an alternative you cshow the list is a currency by setting the Column Heads property to Yes and changing the column name in the row source query to (eg) US$, Value: FieldName
Alternatively you use a sub form set to data sheet mode, which will show the currency formatting
HTH
vtd
In the SQL being used as the RowSource for the ListBox, instead of using the CurrencyField, use a calculated Column with expression like:
CODE
Format([CurrencyField], "Currency") As FormattedCurrency
Alan_G
Or you could also use
CODE
FormatCurrency([CurrencyField]) As FormattedCurrency
mrmagoo_83
Here is my SQL statement, where should I insert this format code?
CODE
SQL_Text = "SELECT [PurchaseReq].[Recno],[PurchaseReq].[Date], [PurchaseReq].[Name], [PurchaseReq].[Natural Acct #], [PurchaseReq].[Project #], [PurchaseReq].[Capital WO #], [PurchaseReq].[Routing 1], [PurchaseReq].[Grand Total], [PurchaseReq].[Page #], [PurchaseReq].[Of Pages]" & _
" FROM [PurchaseReq] WHERE " & _
"(([PurchaseReq].[Name]=Forms![Breakdown]![Name] OR Forms![Breakdown]![Name] IS NULL)" & _
" AND ([PurchaseReq].[Routing 1]=Forms![Breakdown]![Status] OR Forms![Breakdown]![Status] IS NULL)" & _
" AND ([PurchaseReq].[Date]>Forms![Breakdown]![After] OR Forms![Breakdown]![After] IS NULL)" & _
" AND ([PurchaseReq].[Date]<Forms![Breakdown]![Before] OR Forms![Breakdown]![Before] IS NULL)" & _
" AND ([PurchaseReq].[Grand Total]=Forms![Breakdown]![Grand] OR Forms![Breakdown]![Grand] IS NULL)" & _
" AND ([PurchaseReq].[Natural Acct #]=Forms![Breakdown]![Acct] OR Forms![Breakdown]![Acct] IS NULL)" & _
" AND ([PurchaseReq].[Project #]=Forms![Breakdown]![Project] OR Forms![Breakdown]![Project] IS NULL)"
Alan_G
If the field you want displayed as currency is the [Grand Total], you'd change [PurchaseReq].[Grand Total] to
ormatCurrency([PurchaseReq].[Grand Total]) As GTotal
but, as you say that your table field is a Currency datatype and you're using A2003, I'm guessing that you've installed SP3 for A2003 without also installing the Hotfix for it. SP3 for Access introduced an awful lot of well documented bugs, one of which is the one you're currently experiencing.
If you've installed SP3 then you need to install the hotfix from here and you shouldn't need to apply any formatting...........
mrmagoo_83
Wow, you have to be kidding me, that was the problem, sheesh. Thanks, I really appreciate that, fixed me right up. Going to have to find out what other items IT has installed that now have hot fixes out.
Alan_G
You're welcome - glad you got it sorted sad.gif
P3 was something of a nightmare when it was first released.........
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.