Search code examples
sqlms-accessms-access-2016

Finding duplicate entries with Long Text data type in Access SQL

I have a query that returns the following error message upon running it: Invalid Memo, OLE, or Hyperlink Object in subquery 'field1'

My query is shown below:

SELECT field1, field2, field3
FROM table1
WHERE (((table1.[field1]) In (SELECT [field1] FROM [table1] As Tmp GROUP BY [field1] HAVING Count(*)>1 )))
ORDER BY fie1dl1;

Any ideas on how to fix this?

Thanks!

Solution

  • (writing as an answer just because as a comment it would be a mess - might be an answer if the requirement is provided)

    I don't use access, but from the message as I understand grouping on long text is not supported. As a workaround, if your long text data could be identified in their first 255 characters (access supports 255 in length in short text? to be on the safe side let's say 250):

    SELECT *
FROM table1
WHERE left([field1],250) In (SELECT left(field1,250) 
   FROM [table1] 
   GROUP BY left([field1],250) HAVING Count(*) > 1 )
ORDER BY field1;

    PS: If access have a CheckSum calculating function, then you could use it for comparison instead of Left(field1, 250).