Thank you Allen....however I can't get it to work...if I run the sql query
below, Access prompts me for a field called Currency...it's not
recognizing
the format statement and considers Currency to be a field...
I simplified the test to be
SELECT Format([rate_discussed], Currency) from leads_CDMi
it prompted me for a field called currency
here's the actual SQL statement...this is driving me crazy, please,
please,
please take a look at it and see if anything jumps out at you.
SELECT leads_CDMi.assignedto as [Assigned To], leads_CDMi.Status,
leads_CDMi.CompanyName as [Company Name], leads_CDMi.vendorname as [Vendor
Contact], leads_CDMi.modifiedon as [Modified On],
vendor_names_CDMi.VendorTitle as [Title], leads_CDMi.Priority,
leads_CDMi.saleslifecycle as [Sales LifeCycle],
vendor_names_CDMi.VendorState
AS [State], leads_CDMi.call_back_date as [Call Back],
leads_CDMi.estimated_project_start as [Project Start],
Format([leads_cdmi.rate_discussed], Currency) as [Rate],
leads_CDMi.potentialproduct as [Potential Product],
leads_CDMi.potentialservice as [Potential Service],
leads_CDMi.sourceoflead
as [Source of Lead] FROM leads_CDMi INNER JOIN vendor_names_CDMi ON
(leads_CDMi.CompanyName = vendor_names_CDMi.VendorCompany) AND
(leads_CDMi.VendorName = vendor_names_CDMi.VendorContactName) AND
(leads_CDMi.VendorName = vendor_names_CDMi.VendorContactName) where
leads_CDMi.assignedto like '*Peter A. Mollica*' and leads_CDMi.status like
'*Open*' and leads_CDMi.modifiedon between #8/1/2002# and #12/31/9999#
ORDER
BY companyname desc
Allen Browne said:
Use the Format() function to turn the data into a string that displays as
currency:
SELECT MyID, Format([MyField], Currency) AS MyFieldCurr FROM MyTable;
You are aware that Access gives wrong results with the Decimal data type?
See:
http://members.iinet.net.au/~allenbrowne/bug-08.html
I have a multi-column listbox on a form. I'm populating the listbox by
creating an sql statement and using that statement as the .RowSource
for
the
listbox. The data entry field for this field has a format as Currency.
When
a user enters $50.50 as the value, it is stored in the table and is
displayed
in the listbox as 50.5 The columnsin the table is defined as
decimal(5,2).
I need to format the sql select statment to format that decimal field
as
$123.45 for the listbox. Is there a way to format the decimal (5,2)
column
as $123.45 in the listbox using an sql statement for the
listbox.RowSource?
Thanks in advance!
