Selecting latest value

try the following;

select [Field1],Max(CInt([Number]))
From Table1
Group by [Field1]

actually, not quite right, bare with me

lets say col1 have IR value nd col2 1.0

then try this

Select col1 , Max(col2) from table_name
where col1='IR 0002'

you cna get for all also
Select col1 , Max(col2) from table_name
group by col1

Thanks.

I'm trying to get a value from access into excel.  So far I have this in excel:

Public Function GetADORecordset(ByVal strSql As String) As ADODB.Recordset

    Dim cn As New ADODB.Connection
    Dim rst As ADODB.Recordset
   
    Set cn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = cn
   
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenStatic
    'rst.LockType = adLockReadOnly
    rst.LockType = adLockOptimistic
    rst.Open strSql
   
    Set rst.ActiveConnection = Nothing
    cn.Close
    Set cn = Nothing
    Set GetADORecordset = rst
   

End Function


Public Function myDLookUpMax(ByVal strField As String, ByVal strDomain As String, _
    Optional strCriteria As Variant)

    Dim rsLk As Recordset
    Dim strSql As String
   
    myDLookUpMax = Null
    If Not IsMissing(strCriteria) Then
        strSql = "SELECT MAX (" & strField & ") FROM " & strDomain & " WHERE " & strCriteria
    Else
        strSql = "SELECT MAX (" & strField & ") FROM " & strDomain
    End If
    MsgBox (strSql)
   
    Set rsLk = GetADORecordset(strSql)
    If Not rsLk.EOF Then myDLookUpMax = rsLk(0) Else myDLookUpMax = "N/A"
    rsLk.Close

End Function

and I call it with this in excel:

=myDLookUpMax("Field6","Docs","Field3 = 0079")

However all I keep getting is #VALUE!

Anything look out of place?

Thanks.

I'm trying to get a value from access into excel.  So far I have this in excel:

Public Function GetADORecordset(ByVal strSql As String) As ADODB.Recordset

    Dim cn As New ADODB.Connection
    Dim rst As ADODB.Recordset
   
    Set cn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = cn
   
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenStatic
    'rst.LockType = adLockReadOnly
    rst.LockType = adLockOptimistic
    rst.Open strSql
   
    Set rst.ActiveConnection = Nothing
    cn.Close
    Set cn = Nothing
    Set GetADORecordset = rst
   

End Function


Public Function myDLookUpMax(ByVal strField As String, ByVal strDomain As String, _
    Optional strCriteria As Variant)

    Dim rsLk As Recordset
    Dim strSql As String
   
    myDLookUpMax = Null
    If Not IsMissing(strCriteria) Then
        strSql = "SELECT MAX (" & strField & ") FROM " & strDomain & " WHERE " & strCriteria
    Else
        strSql = "SELECT MAX (" & strField & ") FROM " & strDomain
    End If
    MsgBox (strSql)
   
    Set rsLk = GetADORecordset(strSql)
    If Not rsLk.EOF Then myDLookUpMax = rsLk(0) Else myDLookUpMax = "N/A"
    rsLk.Close

End Function

and I call it with this in excel:

=myDLookUpMax("Field6","Docs","Field3 = 0079")

However all I keep getting is #VALUE!

Anything look out of place?

SELECT Table1.Field1, Max(Table1.Field2) AS MaxOfField2
FROM Table1
GROUP BY Table1.Field1;

andyw27,

Please define what you mean by "latest number".  If what you mean is, "the last entry for a given value of <column 1>", this will not be possible unless you have a sequential AutoNumber column on the table, or a column that has a date/time stamp for when the entry was made.

Or perhaps do you simply want the maximum value, which is not necessarily the same thing?

Also,


What are ALL of the ways that value can be represented?

Patrick