Query error - "expression not part of aggregate function"

SELECT Nz(Min([ApptDate]),#1/1/1000#) AS NextAppt, Appt_ID
FROM tblAppointments
WHERE Entity_ID=[Forms]![frm0]![Entity_ID] AND ApptDate>=Date()

If I do not include Appt_ID, it works fine. But I need the ID so I can join
other queries to this one. My guess is this is an elementary error...

Thanks in advance.

re: Query error - "expression not part of aggregate function"

Anytime you use an aggregate function such as Min, Max, Avg, Count, etc, you
need to use a GROUP BY expression for all of the other fields not in an
aggregate function:

SELECT Nz(Min([ApptDate]),#1/1/1000#) AS NextAppt, Appt_ID
FROM tblAppointments
WHERE Entity_ID=[Forms]![frm0]![Entity_ID] AND ApptDate>=Date()
GROUP BY Appt_ID


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:SCXDd.480$JI3.63@newssvr14.news.prodigy.com.. .[color=blue]
> SELECT Nz(Min([ApptDate]),#1/1/1000#) AS NextAppt, Appt_ID
> FROM tblAppointments
> WHERE Entity_ID=[Forms]![frm0]![Entity_ID] AND ApptDate>=Date()
>
> If I do not include Appt_ID, it works fine. But I need the ID so I can
> join
> other queries to this one. My guess is this is an elementary error...
>
> Thanks in advance.
>
>[/color]

re: Query error - "expression not part of aggregate function"

> SELECT Nz(Min([ApptDate]),#1/1/1000#) AS NextAppt, Appt_ID[color=blue]
> FROM tblAppointments
> WHERE Entity_ID=[Forms]![frm0]![Entity_ID] AND ApptDate>=Date()
> GROUP BY Appt_ID[/color]

Ah! I had tried GROUP BY AppDate...

Thanks for the help!