Custom SQL Query?

[Matt's Challenge.com]

Where do I insert the code to format the result to only 1 decimal place?
--

SELECT Registrant_Information.Gender,
(Avg([Date_of_Diagnosis]-[Date_of_Birth])/365) AS GAveDxAge,
(Avg([Walk_End_Date]-[Date_of_Diagnosis])/30) AS GAveDxWalk,
(Avg([Talk_End_Date]-[Date_of_Diagnosis])/30) AS GAveDxTalk,
(Avg([Eat_End_Date]-[Date_of_Diagnosis])/30) AS GAveDxEat,
(Avg([Breath_End_Date]-[Date_of_Diagnosis])/30) AS GAveDxBreath,
(Avg([Date_of_Death]-[Date_of_Diagnosis])/30) AS GAveDxLive
FROM Registrant_Information
GROUP BY Registrant_Information.Gender
ORDER BY Registrant_Information.Gender;

 

[Jim Buyens]

For example:

Format(Avg([Date_of_Diagnosis]-
[Date_of_Birth])/365,"#,##0.0") AS GAveDxAge,

Keep in mind that this truncates rather than rounds. That
is, 123.19 would come out 123.1.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[Matt's Challenge.com]

Jim thank you for replying.The suggestions you made I tried. There were some
errors in schema. Did I miss something. I have seen other threads that have
FormatNumber you have just Format

For example:

Format(Avg([Date_of_Diagnosis]-
[Date_of_Birth])/365,"#,##0.0") AS GAveDxAge,

Keep in mind that this truncates rather than rounds. That
is, 123.19 would come out 123.1.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

-----Original Message-----
Where do I insert the code to format the result to only 1 decimal place?
--

SELECT Registrant_Information.Gender,
(Avg([Date_of_Diagnosis]-[Date_of_Birth])/365) AS GAveDxAge,
(Avg([Walk_End_Date]-[Date_of_Diagnosis])/30) AS GAveDxWalk,
(Avg([Talk_End_Date]-[Date_of_Diagnosis])/30) AS GAveDxTalk,
(Avg([Eat_End_Date]-[Date_of_Diagnosis])/30) AS GAveDxEat,
(Avg([Breath_End_Date]-[Date_of_Diagnosis])/30) AS GAveDxBreath,
(Avg([Date_of_Death]-[Date_of_Diagnosis])/30) AS GAveDxLive
FROM Registrant_Information
GROUP BY Registrant_Information.Gender
ORDER BY Registrant_Information.Gender;