CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.

YesSoftware Forums -> CodeCharge Studio -> ASP

 Calculate highest value and assign to a variable

Print topic Send  topic

Author Message

Posts: 22
Posted: 07/03/2012, 9:17 AM


I am trying to calculate the highest value from a table and assign it to a varaible so I can use the variable result in an IF Then Else statement that will assign a string dependent on the results.

Eg. If the highestvalue is between 0 - 5 then it will result in "Poor", 6 - 10 = "Average", etc.

I have two tables: TblProperty and TblResults.
The two tables are linked together by an id 'PropertyID and 'ResultsID'. Each property will have a number of values associated with it (approx. 68 per property)

Within TblResults I have a column called 'Score' and there will be 68 values that are associated to each individual property.

I am trying to find out the highest value from the 'Score' column ( range: 0 - 25)

Hopefully I am trying to calculate this in the correct place:

I have created custom code in the "Before Build Select" event:

Dim Connection
Dim highestvalue

Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "TestServer"

SQL = "SELECT MAX(Score) as 'highestvalue' FROM TblResults"
Set highestvalue = Connection.Execute(SQL)

Set Connection = Nothing

I have created a label called 'highestvalue' and nothing is diaplayed.

I have even put a response.write ("Highest Risk is " & highestvalue) and I do not get a value.

I have tried different ways of assigning the results of my SQL statement to a variable but no luck, can someone please tell me where I am going wrong?

The If then else statement I am using is in the main ASP code, so please advise me if my variable will be read from there or if I need to write the highest value to the SQL database and then read the result in the if then else statement.

Thanks in advance.

View profile  Send private message

Posts: 22
Posted: 07/04/2012, 8:56 AM


I have fixed my SQL statement to:

SQL = "SELECT MAX(Score) AS maxvalue FROM TblResults WHERE PropertyID = 'B25015'"
Set RecordSet = Connection.Execute(SQL)
highestvalue = RecordSet("maxvalue")

I get the correct result and it displays in a Response.Write

How do I read the variable value in the main ASP code?

Do I create a new column in the SQL database and do a INERT INTO VALUES or is there another way of reading the variable value?

Please advise.

Thanks in advance
View profile  Send private message

Add new topic Subscribe to topic   

These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.

Home   |    Search   |    Members   |    Register   |    Login

Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by  and YesSoftware, Inc.