Maximum Number from the table field

[rumy]

I am using Access

I want to know the maximum number from the Amount field. Will you please help me?

[hassa046]

Amount of what?

as far as i know, it's a long as the vb6 "Long" variable type (-*2,147,483,648 through 2,147,483,647), but i'm not entirely sure.

[brucevde]

Or do you mean

Select Max(Amount) From TableName

[jmcilhinney]

If you mean you want to get just the maximum value from the table in Access then it would be:

Dim con As New OleDbConnection("connection string here")
Dim cmd As New OleDbCommand("SELECT MAX(Amount) FROM MyTable", con)
 
con.Open()
 
Dim result As Object = cmd.ExecuteScalar()
 
con.Close()
 
If result Is DBNull.Value Then
    'The query returned a null value.
    'That indicates that there is no maximum value because there are no records.
Else
    Dim maxAmount As Decimal = CDec(result)
 
    'Use maxAmount here.
End If

See www.connectionstrings.com for the appropriate connection string format.

If you already have the records in a Datatable then it would be similar but different:

Dim result As Object = myDataTable.Compute("MAX(Amount)")
 
If result Is DBNull.Value Then
    'The query returned a null value.
    'That indicates that there is no maximum value because there are no records.
Else
    Dim maxAmount As Decimal = CDec(result)
 
    'Use maxAmount here.
End If

In either case if you know for a fact that the table contains records then you can omit the check for a null result, e.g.

If myDataTable.Rows.Count > 0 Then
    Dim maxAmount As Decimal = CDec(myDataTable.Compute("MAX(Amount)"))
 
    'Use maxAmount here.
End If