Query pulling too many decimal spots in certain field.

[Prayder]

I have a query that runs and has a field called Freight and Taxes. When the currency value populates in that field it will give some number like


"0.265295358649789" but I only want it to show two spots to the right of the decimal. This field pulls from two different tables that are linked together. I have checked the tables to ensure that the decimal property is set but when the query runs they still show up with the elongated number after the decimal.

Please someone Help!!!

[orange]

I don't think a currency datatype will show that many decimal places. Are you sure it is a currency datatype?
Please show the field definitions and the query sql involved.

from http://eggerapps.at/mdbviewer/docs/e...s.htmlCurrency The currency type is a special kind of decimal, with up to 4 digits on the right of the decimal point and up to 15 on the left. It was introduced for financial data and is available in all versions of Access. This type uses 8 bytes of disk space.

[Prayder]

Here is the query SQL:

SELECT PO.PONumber, PO.PODate, PO.VendorID, VENDORS.Vendor, VENDORS.[Fax Number], VENDORS.[Phone Number], VENDORS.address, [City] & " " & [State] & ", " & [Zip Code] AS Address2, PO.OrderedBy, PO.Date_Required, PO.FOB, RequisitionersData.Phone, RequisitionersData.Fax, PO_Details.ProjectID, PO_Details.ProjectLine, PO_Details.VendorPart, Parts.Location, IIf(IsNull(RequisitionDetails.notes),PO_Details.Pa rtDesc,PO_Details.Partdesc & " / Notes: " & RequisitionDetails.notes) AS PartDesc, PO_Details.OrdQtyMade, Unit_Types.Name AS PName, Unit_Types_1.Name AS OName, PO_Details.PartID, IIf(IsNull([requisitionno])<1,PO_Details.price,requisitiondetails.price) AS Price, IIf(IsNull([requisitionno])<1,PO_Details.price*PO_Details.ordqtymade,requisi tiondetails.price*[qty]) AS LineTotal, RequisitionDetails.RequisitionNo, Requisition.Rush, PO.ClosedDate, PO_Details.ClosedBy, PO_Details.RcvdDate, PO_Details.RcvdBy, PO_Details.OrdQtyRcv, PO_Details.VendorInv, PO_Details.Voided, PO_Details.[Org Req Price], Requisition.Approved_By, Requisition.MadeBy, Employees1.[Last Name], IIf(IsNull([projects].[ProjectName]),"Stock Replenishment",[projects].[ProjectName]) AS projectname, [GL Accounts].[Account Description], [GL Accounts].[GL account #], PO.Closed, [FNT]*[OrdQtyMade] AS [Freight and Taxes], [FNT]*[OrdQtyMade]+[LineTotal] AS [Total w FNT], IIf(IsNull([gl accounts].[SAP Description]),"UnAssigned",[gl accounts].[SAP Description]) AS [SAP Description], IIf([M&R]=Yes,"M&R","Non-M&R") AS Category, [PartID] & " " & [PartDesc] AS Decription, PO_Details.Price, PO_Details.Price
FROM VENDORS INNER JOIN ((Projects RIGHT JOIN (Employees1 RIGHT JOIN Requisition ON Employees1.Employee_ID = Requisition.MadeBy) ON Projects.ProjectID = Requisition.ProjectID) RIGHT JOIN ((((RequisitionDetails RIGHT JOIN (((((PO INNER JOIN PO_Details ON PO.PONumber = PO_Details.PONum) LEFT JOIN RequisitionersData ON PO.OrderedBy = RequisitionersData.Name) INNER JOIN Parts ON PO_Details.PartID = Parts.PartNum) INNER JOIN Unit_Types ON PO_Details.PriceType = Unit_Types.Type_ID) INNER JOIN Unit_Types AS Unit_Types_1 ON PO_Details.OrdType = Unit_Types_1.Type_ID) ON (RequisitionDetails.POLine = PO_Details.LineNum) AND (RequisitionDetails.PONum = PO_Details.PONum)) LEFT JOIN [GL Accounts] ON Parts.GLAcct = [GL Accounts].[GL account #]) LEFT JOIN [Freight and Taxes Sum totals] ON PO.PONumber = [Freight and Taxes Sum totals].PONumber) INNER JOIN [Freight and Taxes by po item] ON PO.PONumber = [Freight and Taxes by po item].PONumber) ON Requisition.ReqID = RequisitionDetails.RequisitionNo) ON VENDORS.Vendor_ID = PO.VendorID
WHERE (((PO_Details.OrdQtyMade)>0) AND ((PO_Details.Voided)=0));


The field entitled Freight and Taxes is the field that is showing the long decimal number.

[orange]

There is a currency data type specifically created for Currency calculations.

Also, you can use a Format(yourLargeValue,"currency")

see http://www.techonthenet.com/access/f...ric/format.php