Problem using derived column name in Group By

nico5038,

Thank you for your speedy response.  Your suggested SQL works great when executed in native Access, but unfortunately does not work via odbc. Seems like there is something about obdc that requires a slightly different syntax -- quotes or parens or something.  I've tried a few different things, but so far, no success.

Any further ideas.

Lenny

Hmm, strange, try:

select count(*) as cnt, counselorid, mid(shortdate,6,2) & "/" & mid(shortdate,1,4)  as newdate  from meetings group by mid(shortdate,6,2) & "/" & mid(shortdate,1,4), counselorid;

when the backend database is in MSAccess, otherwise it's more efficient to use a "Passthrough" query in the native SQL of the backend database. When you need more info on that check the helpfile to see the merits :-)

Nic;o)

Thanks for the further suggestion. Unfortunately, still doesn't work.

The simpler case,
select count(*) as cnt, counselorid, shortdate as newdate  from meetings group by shortdate, counselorid;
works fine.  Unfortunately, of course, it doesn't produce the result I want.

I'm a bit confused by your last paragraph.  I'm currently accessing the Access mdb on a remote server via a browser page that includes a php-like scripting language to manipulate the db. The server connects to the db via odbc, and I suspect that is where the problem is occcuring.  Don't know how that relates to a "Passthrough" query.

In any case, it appears my problem is either that odbc is demanding a special syntax for the derived column that we haven't hit upon, or it doesn't even support it.  I suspect the former.

Any other hints welcome.

Thanks.

Lenny

Fixed:

Replacing the double quotes (around the slash) with single quotes fixed it.  It then worked using either the plus (+) or ambersand (&) as the concatenating operator.

Just kept trying things until I got it to work.

Awarded points to Nico for the hint of reusing the concatenated columns rather than the dervied column name in the Group By.

Thank you.

Lenny