One-liner causes "Object invalid or no longer set"

[colin_e]

I seem to have developed a talent for breaking Access. I have a table of user
data with near-duplicate rows. I want to pull out the most recent account for
each user based on their name (yes I know this is not robust, the "John
Smith"s will be under-represented, but that's ok).

The subquery here works fine standalone

SELECT MAX(uid) FROM [Accounts Accessed] GROUP BY (lastname + "|" + firstname)

Note: I had to concatenate the first and last name to avoid a problem in
Access with multiple grouping expressions in subqueries.

The full version (still pretty simple) fails with an "Object invalid or no
longer set" error.

SELECT * FROM [Accounts Accessed] AS a WHERE a.uid IN (SELECT MAX(uid) FROM
[Accounts Accessed] GROUP BY (lastname + "|" + firstname));


Is this retrievable, or am I going to have to go down the route of using
intermediate queries?

 

[John Spencer]

Try using a different delimiter than the PIPE | symbol. Use a apace or
a colon. The pipe symbol has a special meaning (I don't know what) and
can cause strange things to happen.

One problem you will have is your table and field names. In Access you
cannot use a sub-query in the FROM clause that includes square brackets.
Since you field names have spaces they require square brackets. This
is one reason to use only letters, numbers, and underscores in your
field and table names. Also, if at all possible avoid naming fields and
tables with reserved words such as "Name", "Date", "Now".

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[colin_e]

Well, I renamed all my tables to alphas and underscores only, and tried
several different delimiters. However this still fails-

SELECT * FROM Accounts_Accessed AS a WHERE a.uid IN (SELECT MAX(uid) FROM
Accounts_Accessed GROUP BY (lastname + "," + firstname));

with the same error- "object invalid or no longer set".

This is a pretty simple query, i'm scratching my head trying to see hat I
have done wrong.
--

Regards: Colin

Try using a different delimiter than the PIPE | symbol. Use a apace or
a colon. The pipe symbol has a special meaning (I don't know what) and
can cause strange things to happen.

One problem you will have is your table and field names. In Access you
cannot use a sub-query in the FROM clause that includes square brackets.
Since you field names have spaces they require square brackets. This
is one reason to use only letters, numbers, and underscores in your
field and table names. Also, if at all possible avoid naming fields and
tables with reserved words such as "Name", "Date", "Now".

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I seem to have developed a talent for breaking Access. I have a table of user
data with near-duplicate rows. I want to pull out the most recent account for
each user based on their name (yes I know this is not robust, the "John
Smith"s will be under-represented, but that's ok).

The subquery here works fine standalone

SELECT MAX(uid) FROM [Accounts Accessed] GROUP BY (lastname + "|" + firstname)

Note: I had to concatenate the first and last name to avoid a problem in
Access with multiple grouping expressions in subqueries.

The full version (still pretty simple) fails with an "Object invalid or no
longer set" error.

SELECT * FROM [Accounts Accessed] AS a WHERE a.uid IN (SELECT MAX(uid) FROM
[Accounts Accessed] GROUP BY (lastname + "|" + firstname));


Is this retrievable, or am I going to have to go down the route of using
intermediate queries?