Min/Max functions - Auto Decimal Places

[ndeans]

I have a stats query that i run on a collection of Waist to Hip Ratio's which are generally measured as a ratio to 3 decimal places as follows:

waist = 89cm
hip = 101cm
WHR = 0.881 (to 3 decimal places)

My problem is that the min and max functions that i am running on this set of data for the group are seem to be returning figures to only 1 decimal place as follows:

a-0.839
b-0.845
c-0.822

Min function will return 0.800 instead of 0.822
Max function will return 0.800 instead of 0.845
Avg function returns 0.835 correctly...

I'm struggling for an answer and pissed at myself for not noticing this way earlier....

All or any help very much appreciated.

Cheers
Nath

 

[HiTechCoach]

Would you please post the the VBA or SQL so we can see how you are using these function?

 

[ndeans]

It's actually in a query but the SQL is as follows:

SELECT Min(qryWHR.WHR) AS MinOfWHR
FROM qryStatSelect01 LEFT JOIN qryWHR ON qryStatSelect01.AssessmentID = qryWHR.AssessmentID
WHERE (((qryWHR.TestCompleteYN)<>0));


Hope this helps

 

[ndeans]

Additional Info: (may or may not be pertinent)

Original data in the table for both hip and waist are set as follows:

Field Type: Number
Field Size: decimal
precision: 4
scale: 1
decimal places: auto

NOTE: given that avg returns a correct figure and min/max do not i can't see this as having an effect.

 

[HiTechCoach]

Can you post the SQL for the query qryWHR?

 

[ndeans]

qryWHR sql as follow:


SELECT tblTest_BodyComp1.AssessmentID, tblTest_BodyComp1.TestDate, tblClientDetails.ClientID, [waist]/[hip] AS WHR, IIf([Gender]="Male",IIf([WHR]<0.9,"Ideal (Low Risk)",IIf([WHR] Between 0.9 And 1,"Moderate Risk",IIf([WHR]>1,"High Risk","Error"))),IIf([WHR]<0.8,"Ideal (Low Risk)",IIf([WHR] Between 0.8 And 0.9,"Moderate Risk",IIf([WHR]>0.9,"High Risk","Error")))) AS WHRRating, IIf([Gender]="Male",IIf([WHR]<0.9,5,IIf([WHR] Between 0.9 And 1,2,IIf([WHR]>1,0,"Error"))),IIf([WHR]<0.8,5,IIf([WHR] Between 0.8 And 0.9,2,IIf([WHR]>0.9,0,"Error")))) AS WHRRanking, tblTest_BodyComp1.waist, tblTest_BodyComp1.hip, tblClientDetails.Gender, IIf([WHRRating]="High Risk",1,0) AS WHRRisk, IIf([Gender]="Male",IIf([waist]>108,1,0),IIf([waist]>95,1,0)) AS WaistRisk, tblTest_BodyComp1.TestCompleteYN
FROM tblClientDetails INNER JOIN tblTest_BodyComp1 ON tblClientDetails.ClientID = tblTest_BodyComp1.ClientID
WHERE (((tblTest_BodyComp1.hip)<>0) AND ((tblTest_BodyComp1.TestCompleteYN)<>0));


The actual calculation is [waist]/[hip] AS WHR, the rest is just 2 forms of risk ratings systems

 

[ndeans]

I have temporarily solved this problem by using the following expressions rather than the straight min/max functions:

WHRMin: Min([WHR] * 1000)/1000
WHRMax: Max([WHR] * 1000)/1000

For some reason shifting the decimal place by 1000 allows the min/max functions to work properly. Subsequently this is a solution of sorts but it has not necessarily restored my faith in the min/max functions just yet.

Cheers for any continued help
Nath