VBA to return max value of field in a table 1

[osx99]

I have the following code which works fine, however, I want to look up the maximum value in one field of the table

i.e. return the maximum value for field 'ID Count' in table 'thursdaytable'

This code will sit in and excel worksheet rather than in Access.

Can someone help?

Many thanks
Os

Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database

Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("C:\Documents and Settings\Desktop\db1.mdb")
    
' open the database
    Set rs = db.OpenRecordset("thursdaytable", dbOpenTable)

        With rs
  
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("oskn1") = Range("a4").Value
            .Fields("oskn2") = Range("a5").Value
            .Fields("buttonhole") = Range("b6").Value

            .Update ' stores the new record
        End With

    'Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

 

[JBinQLD]

g'day,

dmax is the function in access, no idea about xl though!

JB

 

[PHV]

A starting point:

Set rs2 = db.OpenRecordset("SELECT Max([ID Count]) FROM thursdaytable")
MsgBox rs2.Fields(0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[osx99]

Wonderful! thanks!