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" <jgaines_new...@yahoo.co.uk> 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 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?
>
> --
> 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
<38af4087-f7c8-47e3-acae-(E-Mail Removed)> 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" <(E-Mail Removed)>
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 <(E-Mail Removed)> 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" <(E-Mail Removed)>
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]