http://www.fmsinc.com/microsoftaccess/query/distinct_vs_distinctrow/unique_values_records.asp

DISTINCT

DISTINCT checks only the fields listed in the SQL string and then eliminates the duplicate rows. Results of DISTINCT queries are not updateable. They are a snapshot of the data.

DISTINCT queries are similar to Summary or Totals queries (queries using a GROUP BY clause).
DISTINCTROW

DISTINCTROW, on the other hand, checks all fields in the table that is being queried, and eliminates duplicates based on the entire record (not just the selected fields). Results of DISTINCTROW queries are updateable.

As Number-1 indicates, results of using DISTINCT are similar to using an aggregate query, which automatically truncates memo fields and strings to 255 characters.  

DistinctRow does not do that.

Is this truncation by DISTINCT documented somehow?

>>The decision to handle only the first 255 characters is a perfectly reasonable compromise for a desktop database like JET.
Fine.

Three important things:

1
"The DISTINCTROW predicate corresponds to the UniqueRecords property setting" (see image)

2
"The UniqueRecords property has an effect only when you use more than one table in the query and select fields from the tables used in the query. The UniqueRecords property is ignored if the query includes only one table."


3
"The UniqueRecords property applies only to append and make-table action queries and select queries."

http://bytes.com/topic/access/answers/887735-memo-field-gets-truncated-query-results

Crosstab Queries, Summary Queries, Union Queries, and Queries that use Distinct or DistinctRow will all truncate a memo field to 255 characters so Access can perform the required functionality of eliminating duplicates.

Also, if you have specified a format in the field's Format property, this will often truncate the data as well. If Unique Value Property is set to Yes, Access has to compare the values and therefore Memo Field values are truncated.

Number-1,

Interesting that that article indicates that DistinctRow will also truncate memo fields.  Wonder if the reason the OP is getting away with it in the example is that what is being used is a text string of 400 characters, not actually a memo field.

I've rarely found a good reason to use DistinctRow, and for speed purposes, I generally avoid DISTINCT in favor of a GROUP BY clause.

>>Queries that use Distinct or DistinctRow will all truncate a memo field to 255 characters

This does not seem to be the case for my example in my initial question....And there is nothing about Memo type and Field object.

"I generally avoid DISTINCT in favor of a GROUP BY clause. "
Dale ... have you ever run a test on the two, say with a ~2M records, one field ?

@fyed, I suspect you are right about using the text string of 400 characters.

So, why doesn't DISTINCTROW care about performance issues as described by Number-1 so vividly?

btw ... what I posted was from the Access Help file ...

mx

"All,"
?

mx,

Most of the info in the help file is also in MS's online KB, which is linkable.  It is better to go that route for purposes of citing the original source.

Seriously? The Access Help file is instant.  Why would I waste time searching and waiting for a KB on the same subject ?  And of course, you pretty much have to use Google to search the MSKB's

Initially, I forgot to include the Help file source.

@jarmod101
With all due respect, the MS (Access) KB articles are no more or less accurate than the Microsoft Office Access Help File that comes with the product, and from which I have been posting content for the last six years.  In fact, it was Miriam herself that reminded (years ago) that I should mention the content was from the Help File, which I have always done since.

Further, as I noted previously, I am not going to waste my extremely valuable time - much of which I have donated free to EE over the last six years - attempting to get the same content from a MS KB online, a very tedious process at best (try it yourself), when I can get it in seconds from the Help File.

And finally ... re "safest bets" ... whereas I would not question the reliability of trusted sites like FMS and Allen Browne (which I frequently post links from), I certainly would question such sites as  http://bytes.com/topic/access/answers/887735-memo-field-gets-truncated-query-results ... sorry, but you just can't believe everything you read on the internet.

mx

Well, the Help File is certainly original ( even though it generally, sucks, lol) ... just not a link.  

And whenever I post a link, I almost always include a 'title' above the link ... and possibly (likely) why I am posting said link, depending on the context of sorts.

mx

Joe,

the concern now that we didn't have way back when is that sites like EE now get penalized by
Google for copy/pasted content.  For this reason we should be using KB links versus copying content, and I personally have not found that they take much more time to come up with.

Anyhow if you would like to discuss it further, you know how to do so (offline)

Are you telling me that I cannot post content from the Microsoft Office Access Help File - because of Google ?

To other, stumbling on this years after it was posted - this is the power of ad revenue.