Help with query (again)

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

I am exporting the below query to Excel and need some
help. The field called weighted score needs to be
formatted with one decimal point when exported so I can do
some calculations on this field. Instead, it exports 10
decimal places. Does anyone know how I can get my query to
export as 1 decimal point without formating it as text?

SELECT tblAttribute.AttributeID, tblAttribute.[#Checked],
Sum(IIf([#Ded] Is Null,0,[#Ded])) AS NumDed, IIf([NumDED]
Is Null,[#Checked],[#Checked]-[NumDed]) AS [Audit Score],
Format(IIf([NumDed] Is Null,100,(IIf([NumDED] Is Null,
[#Checked],[#Checked]-[NumDed])/[#Checked])),"0.0%") AS
TotalPoints, tblAttribute.WeightPoints, IIf([NumDed] Is
Null,[tblAttribute].[WeightPoints],(([tblAttribute].
[#Checked]-[NumDed])/[tblAttribute].[#Checked])*
[tblAttribute].[WeightPoints]) AS WeightedScore
FROM tblPrevAudit RIGHT JOIN tblAttribute ON
tblPrevAudit.AttributeID = tblAttribute.AttributeID
GROUP BY tblAttribute.AttributeID, tblAttribute.
[#Checked], tblAttribute.WeightPoints;
 
Try using the Round function:

(Round(([tblAttribute].[#Checked]-[NumDed])/[tblAttribute].[#Checked])*[tblAttribute].[WeightPoints],1))
AS WeightedScore

Alternatively,

(CLng(((([tblAttribute].[#Checked]-[NumDed])/[tblAttribute].[#Checked])*[tblAttribute].[WeightPoints])*10)/10)
AS WeightedScore
 
Back
Top