How to limit MIN(DateValue) with GROUP BY in query?

[deko]

How do I limit the results of this query to return only the NextAppt and the
corresponding Subject?

SELECT [Subject], MIN([ApptDate]) As NextAppt
FROM tblAppointments
WHERE ((tblAppointments.Entity_ID=QryPrm("frm0", "Entity_ID")) AND
(tblAppointments.ApptDate>=Date()))
GROUP BY [Subject];

The problem with this is that it returns more than one value. If
tblAppointments has:

[ApptDate] [Subject]
2/10/2005 meetingA
2/11/2005 meetingB
2/12/2005 meetingC

I am getting is every record that satisfies the WHERE clause.

What I want is:

2/10/2005 meetingA

Thanks in advance.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What's 'QryPrm("frm0", "Entity_ID")'? is it a call to a function? Does
the function read the Control "Entity_ID" on the form "frm0"? If so,
use the query PARAMETERS instead:

PARAMETERS Forms!frm0!Entity_ID Long;
SELECT [Subject], MIN([ApptDate]) As NextAppt
FROM tblAppointments
WHERE ((tblAppointments.Entity_ID=Forms!frm0!Entity_ID ) AND
(tblAppointments.ApptDate>=Date()))
GROUP BY [Subject];

If that Entity_ID is a different data type than Long, change it
accordingly.

If that Entity_ID on the form has more than one Subject, there will be
as many rows returned as there are subjects.

Do you want the first NextAppt or the last NextAppt. For the first:
use MIN(NextAppt). For the last: Max(NextAppt).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQeiIxIechKqOuFEgEQJi+gCgs8VnS6gSEd0DVjgDwAp5tx DF2JEAn0mp
uy4oefXoz7cHZFIN6ig9xf3j
=dJkJ
-----END PGP SIGNATURE-----
deko wrote:

How do I limit the results of this query to return only the NextAppt and the
corresponding Subject?

SELECT [Subject], MIN([ApptDate]) As NextAppt
FROM tblAppointments
WHERE ((tblAppointments.Entity_ID=QryPrm("frm0", "Entity_ID")) AND
(tblAppointments.ApptDate>=Date()))
GROUP BY [Subject];

The problem with this is that it returns more than one value. If
tblAppointments has:

[ApptDate] [Subject]
2/10/2005 meetingA
2/11/2005 meetingB
2/12/2005 meetingC

I am getting is every record that satisfies the WHERE clause.

What I want is:

2/10/2005 meetingA

[deko]

Hi and thanks for the reply.

What's 'QryPrm("frm0", "Entity_ID")'? is it a call to a function? Does
the function read the Control "Entity_ID" on the form "frm0"?
Yes and yes. Here is the function:

Public Function QryPrm(ByVal strFrm As String, ByVal strCtl As String,
Optional ByVal strSubFrm As String) As Variant
If Len(strSubFrm) = 0 Then
QryPrm = Forms(strFrm).Controls(strCtl)
Else
QryPrm = Forms(strFrm).Controls(strCtl).Form.Controls(strSu bFrm)
End If
End Function
If so, use the query PARAMETERS instead:

PARAMETERS Forms!frm0!Entity_ID Long;
SELECT [Subject], MIN([ApptDate]) As NextAppt
FROM tblAppointments
WHERE ((tblAppointments.Entity_ID=Forms!frm0!Entity_ID ) AND
(tblAppointments.ApptDate>=Date()))
GROUP BY [Subject];

I thought it was better to supply query parameters with a function as I am
doing. One advantage of using a function to supply parameters is that I can
build a recordset from my queries like this:

Set rst = db.OpenRecordset("qryMyQuery")

which I cannot do if I write the query with a reference to a control on a
form.

I looked up Parameter in Help: "Parameter objects allow you to change the
arguments in a frequently run QueryDef object without having to recompile
the query."

Since my query is a stored query, I'm not sure PARAMETER is what I want. In
any case, regardless of how I supply the parameter, I still don't get the
one, first NextAppt I am looking for. Am I missing something?