RE: Max again
- From: ooxx <ooxx@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 30 Oct 2008 21:58:01 -0700
Hi ,
I can change that to "asc" , Thanks to the thread.
"ooxx" wrote:
Hi,.
In this case, I 'd like to know the min value. I've notice the "top" , so
what would be if it would be the min value. I know you're waiting for this
question,if my wonder was correct.
Thanks,
"Dale Fye" wrote:
A while back, I created a function (similar to DLOOKUP) that will identify
the Nth largest value (distinct values). It is pretty self explanitory.
You pass it the field name, table name, which value (N) and an optional
critieria. It returns a variant to allow it to return NULL values, which it
does if:
a. the query returns no results
b. there are less than N distinct values in the field
Public Function fnNthLargestValue(FieldName As String, TableName As String, _
N As Integer, Optional Criteria As Variant
= Null) As Variant
Dim strSQL As String
Dim rs As DAO.Recordset
Dim intLoop As Integer
strSQL = "SELECT Top " & N & " [" & FieldName & "] " _
& "FROM [" & TableName & "] " _
& ("WHERE " + Criteria + " ") _
& "GROUP BY [" & FieldName & "] " _
& "ORDER BY [" & FieldName & "] DESC"
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If rs.EOF Then
fnNthLargestValue = Null
Else
rs.MoveLast
If rs.RecordCount < N Then
fnNthLargestValue = Null
Else
rs.MoveFirst
For intLoop = 1 To N - 1
rs.MoveNext
Next
fnNthLargestValue = rs(0)
End If
End If
rs.Close
Set rs = Nothing
End Function
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
"ooxx" wrote:
Hi,
I am really shy to ask this question again since then from subject "Max no
group" .
You can refer to sample data.
SELECT *
FROM myTable
WHERE f3= ( SELECT MAX(f3)
FROM myTable as X
WHERE X.F1 = MyTable.X1 )
As I have applied this to my form code as shown below:
It was to much help for me, Thanks.
CurrentDb().Execute _
"Update myTable Set f3 = 99 WHERE f3 = (SELECT MAX(f3) FROM myTable as X
WHERE X.F1 = '" & Me.T1 & "')"
This is the code and it was on me in finding max value of "f3" to update
record of "f3" according to "T1" control textbox.
By the way, I 'd like to have the code to find the second max value or third
of "f3" with only single code. hehehe... I try that but it took me too much
storage of text code inside. I'd like short.
Anybody help?
Thanks,
- References:
- Max again
- From: ooxx
- RE: Max again
- From: Dale Fye
- RE: Max again
- From: ooxx
- Max again
- Prev by Date: How do I use a class property as criteria in an Access query?
- Next by Date: Removing partial character strings
- Previous by thread: RE: Max again
- Next by thread: Conteggio date
- Index(es):
Relevant Pages
|
