Why DISTINCT causes error 3464

[ggfota]

Hi

I have a problem with SELECT DISTINCT query.
I'm trying to solve why it causes error 3464.

Guess that problem might be cause by one field.
The source table has text(17) field from which I need to extract 'YYMMDD' date and convert it to date/time format. I convert only from not null and 17char strings

I use following function in SQL statement

IIf(Len(Trim(D.NRPZ))=17 and not D.NRPZ is null,DateValue(Format(Mid(D.NRPZ,8,6),"0000-00-00")))

Source for this query is a subquery that joins two external DBF tables (about 500000 records).

1.
What i have found that top 6991 do not cause the problem but 6992 do.
I have made copy of these 6992 record and put into local table.
The original query works fine on this data set

2. (with subquery limited to top 6992 as source)
Query with only one field with the above function - works fine
Query with 14 fields + one with the above function - works fine
Query with 15 fields + one with the above function - DOES NOT WORK !!!
I have used 15th field instead of 14th to see if this caused the problem but not - works fine
I've added 15th field that is existing field wrapped in MID function - works fine
I've added 16th field that is existing text field joined with & - works fine
I add 17th field numeric, that is new field from source subquery, - DOES NOT WORK !!!
Query with all fields except the one with the above function - works fine

As soon as I remove DISTINCT the query works fine.
I've tried to debug the problem but as I wrote in point 1. local copy of 6992 records work fine for the query.

The error message itself does not help anyway to solve the problem.
Making it one more reason to hate this M$ product.

My question are:
Can anybody explain what exactly could couse the problem?
Is there known limit to use of DISTINCT in Access?

[missinglinq]

The problem is that the use of DISTINCT in Access produces a recordset that cannot be updated, which is what you're actually trying to do here.