query to report 3 decimals 1

[williekay]

I got this query. I got into the properties of the %forNULL field, set it 3 decimals, set it to percent. What is happing is this the sum of is 75, so 100-75 is 25. The NULLS of is 2, so 25/2 is 12.5. I'm getting 12 ?????


SELECT [Samples1].[Lot No], Sum(IIf(IsNull(),1,0))/(100-Sum()) AS [%forNULL]
FROM Samples1
WHERE (((Left([Lot No],2))="`¥"))
GROUP BY [Samples1].[Lot No];


Willie

 

[TheAceMan1]

How are ya williekay . . . . .

Try:

[blue]SELECT [Samples1].[Lot No], Format(Sum(IIf(IsNull([S]),1,0))/(100-Sum([S])),"#,##0.000") AS [%forNULL][/blue]

See Ya! . . . . . .

 

[williekay]

Something else is happening, I'll have to investigate, this worked, but I'm still getting .120 instead of .125 ??

Willie

 

[TheAceMan1]

williekay . . . . .

What you show is a [purple]Percision[/purple] problem. I've added conversion to [purple]Double Percision[/purple] for the result, if this doesn't work you'll have to add the conversion to other parts of the arithmetic. Try:

[blue]SELECT [Samples1].[Lot No], Format([purple][b]CDbl([/b][/purple]Sum(IIf(IsNull([S]),1,0))/(100-Sum([S]))[purple][b])[/b][/purple],"#,##0.000") AS [%forNULL][/blue]

Or

[blue]SELECT [Samples1].[Lot No], Format([purple][b]CDbl([/b][/purple]Sum(IIf(IsNull([S]),[purple][b]CDbl([/b][/purple]1[purple][b])[/b][/purple],0))/(100-Sum([S]))[purple][b])[/b][/purple],"#,##0.000") AS [%forNULL][/blue]

See Ya! . . . . . .