Access 2000 query with LIKE statement working oddly?

X

xxxxx

Here is the problem (very obvious):

1. SELECT DISTINCT products.details FROM products WHERE
(((products.details) Like "*V1660*"));

results from products.details as follows :
Compatable with: Motorola V-60c/ V-60g/ V-60i/ V-60t/ V-66/ 120c/
120e/ 120t/ 120x/ V-200/ 270c/ 280/ A835/ A830/ A920/ C331 (TDMA)/
C333 (CDMA)/ T720/ T720c/ T720g/ T720i/ T721/ T730/ T733 Nextel iDen
i30sx/ i35s/ i50sx/ i55sr/ i58sr/ i60c/ i80s/ i85s/ i

2. TAKE OUT "DISTINCT" and the full field text is shown in the regular
query graphical grid or a form control, I'm not sure about ADO, but
this database will be running behind a web site... geeezus.

results from products.details as follows :
Compatable with: Motorola V-60c/ V-60g/ V-60i/ V-60t/ V-66/ 120c/
120e/ 120t/ 120x/ V-200/ 270c/ 280/ A835/ A830/ A920/ C331 (TDMA)/
C333 (CDMA)/ T720/ T720c/ T720g/ T720i/ T721/ T730/ T733 Nextel iDen
i30sx/ i35s/ i50sx/ i55sr/ i58sr/ i60c/ i80s/ i85s/ i88s/ i90c/ i95cl/
T720ti/ T721/ T722i/ T730c/ T731 Samsung V1660/ VGA-1000/ A620/ A600/
A460/ A400/ N400/ N240Sanyo SCP-5400/ SCP-5500/ SCP-8100 LG 1200/ 4NE1
KYOCERA 1135/ 2035/ 2119/ 2135/ 2235/ 2255/ 2355/ 2345/ 3225/ 3245/
7135/ Phantom/ Blade/ Rave/ KE4xx series Ericsson T20e/ T20ec/ T20s/
T20sc/ T28/ T28z/T28-world/ T29/ T29s/ T29sc/ A2218Z/ R300D/ R300Z/
R300LX/ R320sc/ R380/ R520M/ R520mc/ T39/ T60d/ T60lx/ T61/ T61z/
T62u/ T68/ T200/ T300/ T306/ T310/ T316/ T610/ T616 LG/ DM-110/
SP-110/ V-111/ SP-510/ DM-510/ TM-510 Touchpoint TP-1100/ TP-2110

What is UP with this? Anyone got any ideas? the field is a MEMO
field... I've sat here for an hour wondering WHY I'm getting back a
record when I don't see the string in the results AT ALL. Then I
looked at the SQL... would an update to the Office package fix this?

This could be happening to other people too. Anyone?
 
J

John Viescas

Access (JET) builds a temporary unique index to solve the DISTINCT, and an
index field can contain only 255 characters. So, the result gets truncated.
I figured out an ugly workaround that eliminates duplicates on the first 255
characters but displays the entire field. I duplicated one record in
Northwind Employees table - one that has a Notes field longer than 255. I
found the DISTINCT ones by doing this:

SELECT Employees.Notes
FROM Employees
WHERE (((Employees.Notes) Like "*a*") AND ((CStr([Notes])) Not In (Select
Cstr(Notes) From Employees As E2 Where E2.EmployeeID >
Employees.EmployeeID)));

You need the CStr function call to extract the first 255 characters -
otherwise the Subquery throws an error.

By the way, if you do this from a web page with ADO, you should use the %
wildcard character instead of *.
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Top