Number Format

[uaguy3005]

I'm having trouble with a calculated value coming up as text instead of a number. I've tried addressing it with the format function, but still no luck. Any ideas on what I can do to get this PPM field to be recognized as a number?



SELECT IIf(Eval(Hour([InspectDateTime]) Between 0 And 6),DateValue([InspectDateTime])-1,DateValue([InspectDateTime])) AS SortDate, tblParts.PartNum, Sum(tblSortData.QtyInspected) AS SumOfQtyInspected, Sum(Nz([TotRej],0)) AS QtyRejected, Format(([QtyRejected]/[SumOfQtyInspected])*1000000,0) AS PPM
FROM tblParts INNER JOIN (tblSortData LEFT JOIN (SELECT tblRejects.SortDataID, Sum(tblRejects.QtyRejected) AS TotRej FROM tblRejects GROUP BY tblRejects.SortDataID) AS qzTotRej ON tblSortData.SortDataID = qzTotRej.SortDataID) ON tblParts.PartID = tblSortData.PartsID
GROUP BY IIf(Eval(Hour([InspectDateTime]) Between 0 And 6),DateValue([InspectDateTime])-1,DateValue([InspectDateTime])), tblParts.PartNum
ORDER BY IIf(Eval(Hour([InspectDateTime]) Between 0 And 6),DateValue([InspectDateTime])-1,DateValue([InspectDateTime]));

Database attached. The query I'm having the problem with is qryTPP. Any help would be appreciated.

[June7]

Format() and FormatNumber() functions return text, not true dates or numbers.

If you need date or number then use appropriate conversion functions: CDate, CInt, CDbl, Val, DateValue. They will error on Null.

Which calculation is issue?

[uaguy3005]

Format() and FormatNumber() functions return text, not true dates or numbers.

If you need date or number then use appropriate conversion functions: CDate, CInt, CDbl, Val, DateValue. They will error on Null.

Which calculation is issue?

Issue was with the Format(([QtyRejected]/[SumOfQtyInspected])*1000000,0) AS PPM, but that seemed to fix it. How do you set it to round the decimal portion? The CInt, returns a #Num! error.

[June7]

Do you really need to format and round in query? Maybe instead use form/report textbox Format property.

[uaguy3005]

Do you really need to format and round in query? Maybe instead use form/report textbox Format property.

Good point. Thanks for help!