SQL Functions in ADO

[vbcodec]

how to have any sql function in ado ? like GETMAX() I don't want to modify my query or requery it I just want to run SQL function and put it into a variable or maybe just move cursor to that record


thanks alot.
S. Mohammad Najafi

[twistedthoughts]

???

[goatsucker]

No different that if it was a normal SELECT query. It is good practice however to give your aggregate function field a name e.g.

SELECT MAX(field1) as maxField1
FROM tbl1

Then you can reference this field by name e.g.

dim intMax as integer
intMax = rst.fields("maxField1").value

If you do not give your field a name and cannot change your SQL query then use the field's number (e.g. in the above example as there is only one field you would use rst.fields(0).value)

I hope this answers your question

[techgnome]

I'm not sure that's quite what he wanted.... I think what he's looking for is some kind of ado function that will return the max of an already existing recordset.... w/o needing to modify the query to return it ..... there is no such built in function.... but you can build one, it's not that difficult.

[magician]

Originally posted by techgnome
I'm not sure that's quite what he wanted.... I think what he's looking for is some kind of ado function that will return the max of an already existing recordset.... w/o needing to modify the query to return it ..... there is no such built in function.... but you can build one, it's not that difficult.

debug.print myrecordset.recordcount

' !!! .Cursorlocation Has 2 b on client side
' !!! myRecordset.Cursrlocation=AdUseClient

[goatsucker]

RecordCount will just return the number of records, not the Maximum value of a particular field

[magician]

Originally posted by goatsucker
RecordCount will just return the number of records, not the Maximum value of a particular field

... Woops, could not understand question ... ;-)

'here is Ur code
' myRecordset is already opened recordset with client-side cursor
' myfield - field U R trying 2 get Max() Value
' just try something like this
 
myRecordset.sort = "[myField] Desc"
myRecordset.moveFirst
debug.print myRecordset.Fields("myField")
 
' Should b Max(Value)
' so, if U'll sort by "Asc", U'll have Min(value)
 
myRecordset.sort = ""
' unsorted recordset, cursor position onthe _first record with Max("MyField")_