select max field in ADO

[trisLOGIC]

How do I write this using ADO.

SELECT MAX(Amount) FROM Table1


What I want is to retrieve the record with the largest number in the Amount field.

Thx

[Jaguar]

Realize that you may have two records where Max(Amount) is exactly the same. What do you want to do then? If you don't care then try this. Done in Access 97 but should work anywhere for SQL. This is just one way of getting to the answer, there may be more.

SELECT TOP 1 test.Field1, Max(test.Amount) AS MaxOfAmount
FROM test
GROUP BY test.Field1

You can't return "*" records because of the aggregate basis of the query. You must use every field name. But this will return the record where the Top 1(st) is the Max of Amount. If you have two records with the same amount that is the hightest, then the record returned will be the Sorted item of the fields left to right.

Hope this helps...

[JHausmann]

The Top operator is not available in SQL Server 6.5. And I've yet to be able to get it to work in 7.0

[trisLOGIC]

How to include variables in the SQL statement ?

by typing
adodc1.recordsource = "Select MAX(Num) Into A FROM TAB"

The system will not recognize A as a variable. Is there some sort of character need to be inserted to differentiate them. I'm sorry.. I couldn't find it in the MSDN.

And is there a way to activate the database without using the Refresh statement again.

I don't know why after I search a record, I try to update it later in the program, I couldn't update it without inserting a Refresh prior to the update. But putting refresh will move my record pointer to the top and I have to do the searhing again.

Thx.

[LG]

A=...
adodc1.recordsource = "Select MAX(Num) Into " & A & " FROM TAB"

[trisLOGIC]

What if I want to store certain data into a variable.

Recordsource = "SELECT MAX(Field) INTO Variable FROM Table"

How to assign the anwser from a query back to a variable.

THx/

[Nina]

maybe like this

With Recordsource
   variable = !Field 'assigns the value of the column field to the variable
End With

it should work when your query returns 1 record

Hope this helps,

[Tonatiuh]

This functions to me:

SELECT amount FROM table1 WHERE amount IN(SELECT MAX(amount) FROM table1)

I hope this functions for you too.

Good Look!

[trisLOGIC]

Is there a possible way to do

SELECT MAX(Num) INTO VarA FROM Tab1


What I want is to assign the result into a local variable VarA.

I can assign local variable to a select statement using & operator. So, there must be a way to reverse the operation right ?

Thx.