Selecting Distinct Memo Fields

[Daffy]

I know that an error will occur when setting up a Query to display Distinct
records of a Memo field. I also know that there is a work around this, which
involves referencing the original table containing the memo field.

My problem is as follows. I have a table containing 3 fields, the last two
being Memos.

Field 1 (Key): Sample#
Field 2: Labeling
Field 3: Core Labeling

I would like to settup a query in which only distinct records (non
truncated) of either Field 2 or Field 3 may be dispalyed. I need this
because I would like the user to have the option of selecting from a list of
existing Labeling and Core Labeling entries when entering data, rather than
typing it all out again.

What i've done but hasn't worked to my full satisfaction:

I Created a Query (qryLabeling) that selects all the entries for the
Labeling field.

SQL:

SELECT tblLabels.Labeling
FROM tblLabels;

Then I created a Query (qryTest) that includes the query above and the
Original table which displays Distinct records of one of the Labeling fields.
This works, but produces trunkated data because as I already know access can
only do comparisons 255 characters in length.

SQL

SELECT DISTINCT qryLabeling.Labeling
FROM qryLabeling, tblLabels;

So does anyone know how to solve the problem of displaying Full Length
Unique (Distinct) memos? I would really really appreciated if someone can
help me with this.

 

[John Spencer]

There really isn't any way of doing this that I know of.

You can do something like the following to get the first 750 characters (and
you could obviously extend this to more parts).

SELECT DISTINCT Mid(MemoField,1,250) as Part1
, Mid(MemoField,251,250) as Part2
, Mid(MemoField,501,250) as Part3
FROM SomeTable

Hope that helps to give you some ideas. But this solution will probably be
fairly slow.

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