Access 2007 - Select Max

[Jeff Gaines]

I want to retrieve the current maximum value of a numeric field in an
Access 2007 table. I am writing the function in C#. I have a select string
as follows:

"SELECT LedgerReference, MAX(LedgerReference) FROM tblLedgerData GROUP BY
LedgerReference";

Although it appears to work, i.e. it doesn't crash, it doesn't actually
return anything.

All the examples I have found so far are quite complex with joined tables,
I just want the maximum value that exists in a single table.

Can anybody give me some guidance please?

 

[XPS350]

I want to retrieve the current maximum value of a numeric field in an
Access 2007 table. I am writing the function in C#. I have a select string
as follows:

"SELECT LedgerReference, MAX(LedgerReference)  FROM tblLedgerData GROUPBY
LedgerReference";

Although it appears to work, i.e. it doesn't crash, it doesn't actually
return anything.

All the examples I have found so far are quite complex with joined tables,
I just want the maximum value that exists in a single table.

Can anybody give me some guidance please?

Maybe this is what you want:
SELECT MAX(LedgerReference) AS Max FROM tblLedgerData


Groeten,

Peter
http://access.xps350.com

 

[Jeff Gaines]

Maybe this is what you want:
SELECT MAX(LedgerReference) AS Max FROM tblLedgerData

Many thanks, Peter
It's the first time I've used the Max function so I will tuck that away in
my library!

 

[John W. Vinson]

I want to retrieve the current maximum value of a numeric field in an
Access 2007 table. I am writing the function in C#. I have a select string
as follows:

"SELECT LedgerReference, MAX(LedgerReference) FROM tblLedgerData GROUP BY
LedgerReference";

Although it appears to work, i.e. it doesn't crash, it doesn't actually
return anything.

All the examples I have found so far are quite complex with joined tables,
I just want the maximum value that exists in a single table.

Can anybody give me some guidance please?

Just constructing a SQL string won't do anything, of course; what are you
*doing* with the string?

As written this will return as many rows as there are values of the
LedgerReference field in the table - probably the whole table - since you're
Grouping by the field. If you just want to return a value in code, you might
do better to call the builtin DMax() domain function:

DMax("[LedgerReference]", "tblLedgerData", <optional criteria>)

 

[Jeff Gaines]

Just constructing a SQL string won't do anything, of course; what are you
doing with the string?

As written this will return as many rows as there are values of the
LedgerReference field in the table - probably the whole table - since
you're
Grouping by the field. If you just want to return a value in code, you
might
do better to call the builtin DMax() domain function:

DMax("[LedgerReference]", "tblLedgerData", <optional criteria>)

Hello John, I am using:

internal static int GetNextLedgerReference()
{
string maxText = "";
string selectString = "SELECT MAX(LedgerReference) FROM " +
JLedgerUpdater.m_TableName;
using (OleDbConnection dbConnection = JLedgerUpdater.GetOleDbConnection())
{
dbConnection.Open();
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(selectString,
dbConnection);
DataSet dataSet = new DataSet();
myDataAdapter.Fill(dataSet, JLedgerUpdater.m_TableName);
OleDbCommand dbCommand = new OleDbCommand(selectString, dbConnection);
maxText = dbCommand.ExecuteScalar().ToString();
}

int maxValue = Convert.ToInt32(maxText);
return maxValue + 1;
}

It does what I want, but if there is a better/more efficient way I am
happy to try it

 

[John W. Vinson]

It does what I want, but if there is a better/more efficient way I am
happy to try it

Not being familiar with C++ or its interaction with Access, I can't really be
of much help. This is anything but a "gettingstarted" question though; I'd
suggest you choose one of the more programming-oriented Access newsgroups!