INT function on ASP page

[charlesb]

I have the following select statement in SQL on a ASP age.

SELECT Weekly_input.Starting_ID, Weekly_input.Weekly_Input_Date, Weekly_input.Week_number, Weekly_input.Foodpacks_taken, Weekly_input.Weight, Weekly_input.Phase, CIF.Phase, CIF.Height, Starting_details.Starting_date, Weekly_input.Counsellor, CIF.Forename, CIF.Surname, Starting_details.CIF_Number, Weekly_input.WID, CIF.[Counsellor ID], Starting_details.Phase_change_date, Starting_details.Management_week_number, Starting_details.Finishing_date, Starting_details.Continuing, Starting_details.Starting_weight, Starting_details.Group_number, Starting_details.Phase, Starting_details.Counsellor_ID, Starting_details.Starting_ID, Int((Weekly_input.Weight/(CIF.Height*CIF.Height))*10 + 0.5)/10 AS BMI , Starting_details.Holding FROM (CIF INNER JOIN Starting_details ON CIF.[CIF Number] = Starting_details.CIF_Number) INNER JOIN Weekly_input ON Starting_details.Starting_ID = Weekly_input.Starting_ID WHERE CIF.[Counsellor ID] = 'grarug' AND Weekly_input.Weekly_Input_Date = '29/Dec/2001' and Starting_details.Continuing = '1' ORDER BY CIF.Surname, CIF.Forename;

Right in the middle is a calculation which works fine without the INT function. However, the calculation needs it. MS-Access allows the INt but this select issues an error on the web page that Int is not a recognised function. Help!!! what can I do.

Cheers

 

[onpnt]

need to but the Int in () I believe for it to work correctly. I would prefer to use the Clng function but the Int is fine as well. Both result in the same basic thing. Try this, let us know if it worked

(Int(Weekly_input.Weight)/(CIF.Height*CIF.Height))*10 + 0.5)/10 AS BMI provide tools to let people become their best.

 

[charlesb]

No it did not work, how does Clng work?

 

[Jorgandr]

Why don't you leave the INT off your SQL Statement and then do a response.write INT(RS("BMI&quot) and see if that works just to test the INT Function itself

 

[charlesb]

I have used INt in VBscript in other pages, the only problem I am having here is trying to do this in a SQL statement. (Note that the int works okay in the SQL code in an Access query).


I have replaced the INT with Convert(int,xxx) where the xxx has the code previously in the () for the INT. This seems to be recognised, however, I only get integers now, and the whole point is that the INt gets the integer value of the calculation then it is divided by 10.

code is now :
(convert(Int,((Weekly_input.Weight/(CIF.Height*CIF.Height))*10 + 0.5)))/10 AS BMI

Still need help though.

 

[forecasting]

It appears that once you convert the first part of the equation to an integer, the second part (where you divide it by ten) also returns an integer. Have you tried converting the convert(int, xxx) to convert(float, convert(int, xxx))/10?

 

[charlesb]

Thanks that worked, great.