How do I set an Aggregate SQL statement result to a control?

[Karen Hill]

I would like to do something like this using VBA in MS Access 2000:

Me.num = "SELECT MAX(num) FROM mytable;"

How does one do that in MS Access correctly? Aggregates in SQL are
SUM, MAX, MIN which return only one value.*
* unless of course they are "grouped by" which I'm not doing...

Thanks for all your help in advance. It is much apprieciated!

[Randy Harris]

"Karen Hill" <ka**********@yahoo.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I would like to do something like this using VBA in MS Access 2000:

Me.num = "SELECT MAX(num) FROM mytable;"

How does one do that in MS Access correctly? Aggregates in SQL are
SUM, MAX, MIN which return only one value.*
* unless of course they are "grouped by" which I'm not doing...

Thanks for all your help in advance. It is much apprieciated!

You should check out the Domain Aggregate Functions - DMax, DMin, DAvg, etc

--
Randy Harris
I'm pretty sure I know everything that I can remember.

[Lyle Fairfield]

DAO
me.num = DBEngine(0)(0).OpenRecordset("SELECT MAX(num) FROM
mytable").Collect(0)

ADO
me.num = CurrentProject.Connection.Execute("SELECT MAX(num) FROM
mytable").Collect(0)

[Thelma Lubkin]

Lyle Fairfield <ly***********@aim.com> wrote:
: me.num
: means
: I can't feel anything in my butt
: or
: I have a form with a control called num on it

: me.num = DBEngine(0)(0).OpenRecordset("SELECT MAX(num) FROM
: mytable").Collect(0)

Thanks: you've got most of the tangles. But is DBEngine(0)(0)
different from 'me' in this context?

--thelma

[Lyle Fairfield]

Thelma Lubkin wrote:

Lyle Fairfield <ly***********@aim.com> wrote:
: me.num
: means
: I can't feel anything in my butt
: or
: I have a form with a control called num on it

: me.num = DBEngine(0)(0).OpenRecordset("SELECT MAX(num) FROM
: mytable").Collect(0)

Thanks: you've got most of the tangles. But is DBEngine(0)(0)
different from 'me' in this context?

Very different:

DBEngine is the top level object of DAO. DBEngine(0) is the first
Workspace. DBEngine(0)(0) is the first Database of the first Workspace.
This should be your Database, the current Database, unless you have run
some very bad wizards which may make themselves DBEngine(0)(0). Many
developers use CurrentDB instead of DBEngine(0)(0) to avoid this
problem. Others, like me, never use wizards, at least not knowingly, or
without a struggle, or unless they are wizards we are convinced are good
wizards.

Me refers to the current class object (form or report), or class, that
is the object where the VBA code in which Me is written. In this case,
Num is a Control, Let Property or Modularly-Scoped Variable of the
current form or report, or Let Property or Modularly-Scoped Variable of
the currently running class.

What the code probably does is look up the maximum value of Num and show
it in a form control called Num.

Thelma I've seen a few posts from you. They were clever and
knowledgeable. One of these is true:

You already knew all this and much more and were just asking to see
where this might go, or for clarification;
Your learning has progressed much further in some areas than others;
I am mistaken about what I read.

In any case there will be a test on Friday.

[Lyle Fairfield]

me.num
means
I can't feel anything in my butt
or
I have a form with a control called num on it

I think it's not expected to be more than one value. Some dbs and db
developers might want to use DOA and some might want to use ADO so I
gave both ways;

What's being collected? The maximum num is being collected ... the kind
one feels after reading a long post from David

0 is the first. When we SELECT MAX we get just one record and that
record has just one field. It's the first field. It's the last field.
It's also the zeroeth field. Think of it as the Alpha and the Omega.
..Collect is a hidden property of recordsets (both DAO and ADO) that
returns a field's value So .Collect(0) = Fields(0).Value. This is the
fastest way, TTBOMK, of getting a field's value other than to set a
reference to the field object and to use the default member of that but
that's pretty difficult if you aren't going to set a pointer to the
recordset. If we had aliased the max field we coulda used the alias as
in
SELECT MAX(num) As MaxNum FROM mytable
then we coulda used .Collect("MaxNum")

Please untangle you? Naah ... I'm only into ropes and knots as they
pertain to canoes and hoisting my food out of the reach or carnivores.

[Thelma Lubkin]

This was Karen Hill's question:

I would like to do something like this using VBA in MS Access 2000:
Me.num = "SELECT MAX(num) FROM mytable;"
How does one do that in MS Access correctly? Aggregates in SQL are
SUM, MAX, MIN which return only one value.*

and this an answer:
Lyle Fairfield <ly***********@aim.com> wrote:: DAO
: me.num = DBEngine(0)(0).OpenRecordset("SELECT MAX(num) FROM
: mytable").Collect(0)

: ADO
: me.num = CurrentProject.Connection.Execute("SELECT MAX(num) FROM
: mytable").Collect(0)

Can someone please explain to me what's going on?
I don't understand what me.num means.
I don't understand why it's expected to be more than one value.
I don't understand what's being collected
I don't understand the 0 parameter in the answer.

Please untangle me.
--thelma

[Lyle Fairfield]

Lyle Fairfield wrote:

Please untangle you? Naah ... I'm only into ropes and knots as they
pertain to canoes and hoisting my food out of the reach or carnivores.

sorry, that's me that I want to hoist out of the reach of carnivores;
my food I want to hoist out of the reach of all the vores.

[Thelma Lubkin]

Lyle Fairfield <ly***********@aim.com> wrote:
: Thelma Lubkin wrote:

:> Lyle Fairfield <ly***********@aim.com> wrote:
: Thelma I've seen a few posts from you. They were clever and
: knowledgeable. One of these is true:

: You already knew all this and much more and were just asking to see
: where this might go, or for clarification;

: Your learning has progressed much further in some areas than others;
/\ /\
|| ||
I'll settle for this one. I did know what 'me' was, but I was
struggling with this DBEngine object, particularly after reading
the reference guide's explanation. I am trying to learn Access without
any formal foundation. My resources are limited to google--a
fantastic resource, but often it's hard to tell google what I
want to know; this group, an even more fantastic resource; and
one unfortunate book that I bought -- it assumes that all you
want to do is click buttons, and all I want to do is to write code.

My major problem is that documentation puts me right to sleep,
so I only read it after a problem has already surfaced. So,
essentially, I know nothing except the limited things that I
have had to find out to get my forms working. (I'm doing this
on a volunteer basis for a non-profit organization, and I don't
get to a computer that runs Access often enough)

I asked the dumb question because I read Access's documentation
on DBEngine and really didn't understand it. Your explanation is
dead on clear: you *do* explain things well, contrary to a
previous statement of yours, and, perhaps even more important,
your answer came in the context of a real live definite example
in front of me.

Thanks for becoming one of my personal tutors on this great
group. Thanks to all the rest of you too, if anyone else is reading.

--thelma

: I am mistaken about what I read.

: In any case there will be a test on Friday.