Prev: How do you create a formula subtracting 1 field into a 2nd field
Next: Problem Generating Key = reprise
From: Jeff Gaines on 22 Mar 2010 12:09

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
From: XPS350 on 22 Mar 2010 12:26
On 22 mrt, 17:09, "Jeff Gaines" <jgaines_new...(a)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 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
From: Jeff Gaines on 22 Mar 2010 12:43
On 22/03/2010 in message
<38af4087-f7c8-47e3-acae-970825c68072(a)z35g2000yqd.googlegroups.com> 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
From: John W. Vinson on 22 Mar 2010 13:11
On Mon, 22 Mar 2010 09:09:03 -0700, "Jeff Gaines" <jgaines_newsid(a)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 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]
From: Jeff Gaines on 22 Mar 2010 13:50
On 22/03/2010 in message <g19fq5dtjaj7qlrth6qrsflm1h0msu23pd(a)4ax.com> 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.
 |  Next  |  Last
Pages: 1 2
Prev: How do you create a formula subtracting 1 field into a 2nd field
Next: Problem Generating Key = reprise