Showing distinct substring value in drop down

[shutterace]

I want to give the user a drop down that includes only the first 3 characters of a string. The string is format is ###-##-### and the field type is char. I have it working but I can't figure out how to only show distinct values in the drop down. Everthing I try gives an error. There are @ 30,000 records with 20 or so prefixes. I hope I explained myself well. Here is the code:

<td>
<%
Set rsPCL = Server.CreateObject(&quot;ADODB.RecordSet&quot
qPCL = &quot;SELECT SUBSTRING([Parcel_Number], 1, 3) AS parcel FROM AZLiens GROUP BY [Parcel_Number] ORDER BY [Parcel_Number]&quot;
rsPCL.Open qPCL, &quot;DSN=;UID=;PWD=&quot;
%>
<form method=&quot;POST&quot; name=&quot;azlienmain2&quot; action=&quot;azlienmain2.asp&quot;>
Parcel Prefix:
<font face=&quot;Arial&quot; size=&quot;2&quot;>
<select name=&quot;parcel&quot;>
<%while NOT rsPCL.EOF%>
<option value=&quot;<%=rsPCL(&quot;parcel&quot%>&quot;>
<%=rsPCL(&quot;parcel&quot%>
</option>
<%
rsPCL.MoveNext
wend
%>
</select>
</font>
</td>

Thanks in advance!
-Jim

 

[shutterace]

Um. Nevermind I figured it out. :~/
qPCL = &quot;SELECT DISTINCT SUBSTRING([Parcel_Number], 1, 3) AS parcel FROM AZLiens GROUP BY SUBSTRING([Parcel_Number], 1, 3) ORDER BY SUBSTRING([Parcel_Number], 1, 3)&quot;

 

[MeanGreen]

Actually, the Group By will give you distinct values.

Hope this helps.