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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

colin_e - 18 May 2008 00:41 GMT

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?

Signature

Regards: Colin

Reply to this Message

John Spencer - 18 May 2008 13:55 GMT

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
[quoted text clipped - 16 lines]
> Is this retrievable, or am I going to have to go down the route of using
> intermediate queries?

Reply to this Message

colin_e - 18 May 2008 21:59 GMT

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.

Signature

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
[quoted text clipped - 34 lines]
> > Is this retrievable, or am I going to have to go down the route of using
> > intermediate queries?

Reply to this Message

raskew - 18 May 2008 22:13 GMT

See http://support.microsoft.com/kb/q147687/ for an explanation of the pipe
symbol usage in Access.

Bob

>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
[quoted text clipped - 19 lines]
>> Is this retrievable, or am I going to have to go down the route of using
>> intermediate queries?

Reply to this Message