Access 2007 - Select Max

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?

--
Jeff Gaines Dorset UK
You can't tell which way the train went by looking at the tracks

On 22 mrt, 17:09, "Jeff Gaines" wrote:
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?

--
Jeff Gaines Dorset UK
You can't tell which way the train went by looking at the tracks

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


Groeten,

Peter
http://access.xps350.com

On 22/03/2010 in message
XPS350
wrote:

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!

--
Jeff Gaines Dorset UK
If it's not broken, mess around with it until it is

On Mon, 22 Mar 2010 09:09:03 -0700, "Jeff Gaines"
wrote:


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)
--

John W. Vinson [MVP]

On 22/03/2010 in message John
W. Vinson wrote:

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 :-)

--
Jeff Gaines Dorset UK
There are 10 types of people in the world, those who do binary and those
who don't.

On Mon, 22 Mar 2010 10:50:57 -0700, "Jeff Gaines"
wrote:

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!
--

John W. Vinson [MVP]