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:SC**************@newssvr14.news.prodigy.com.. .
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.