interesting GROUP BY problem in DRW

[Augie Dawg]

I'm trying to extract information out of a database that contains various
contact information for members of a society and the latest dues payment
they have made using the MAX(PaymentDate) function. I've specified the
phone numbers in data to be returned in formatted form e.g.

FORMAT(MobilePhone,'(###) ###-####') AS "Mobile Phone"

Because I've used an aggregate function in the SELECT clause, I must use the
GROUP BY clause with all items in the SELECT clause (outside of those used
in the aggregate function), but when I do this, the DRW gives me a syntax
error for including [Mobile Phone] in the GROUP BY clause. If I leave it
out, the DRW gives me an error saying I omitted the reference to
'FORMAT(MobilePhone,'(###) ###-####') AS "Mobile Phone"' in my GROUP BY
clause.

Anyone know of a way around this?

Thanks,
A.D.

 

[Jim Buyens]

You have to GROUP BY FORMAT(MobilePhone,'(###) ###-####')

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
|| Microsoft FrontPage Version 2002 Inside Out
|| Web Database Development Step by Step .NET Edition
|| Troubleshooting Microsoft FrontPage 2002
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------