By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,709 Members | 2,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,709 IT Pros & Developers. It's quick & easy.

Query error - "expression not part of aggregate function"

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
> 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


Ah! I had tried GROUP BY AppDate...

Thanks for the help!
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.