Full Version: Format listbox column to display as currency
UtterAccess Forums > Microsoft® Access > Access Forms
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.
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
In the SQL being used as the RowSource for the ListBox, instead of using the CurrencyField, use a calculated Column with expression like:
Format([CurrencyField], "Currency") As FormattedCurrency
Or you could also use
FormatCurrency([CurrencyField]) As FormattedCurrency
Here is my SQL statement, where should I insert this format 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)"
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...........
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.
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.