Driving me Nuts: Wildcards in Access and ADO

[travisbrown]

Querying a Access 2000 db though ADO from ASP page using this query:

SELECT tCaller.c_id, tCaller.c_name, tCaller.c_phone, Count(tTicket.c_fid) AS CountOfc_id 
FROM tCaller LEFT JOIN tTicket ON tCaller.c_id = tTicket.c_fid 
WHERE (tCaller.c_id) <> '' 
GROUP BY tCaller.c_id, tCaller.c_name, tCaller.c_phone HAVING ((tCaller.c_id ALIKE 'facey%') OR (tCaller.c_phone ALIKE '%facey%') OR (tCaller.c_name ALIKE 'facey%')) ORDER BY c_name;

Empty recordest in ADO; returns right results in Access Query Grid.

If I change ALIKE to LIKE in ADO and Access query and the % to * in Access, same results.

[pre]
c_id c_name c_phone CountOfc_id
DFACEY FACEY,DV(DAVID) 604-623-3699 0
[/pre]

The recordset does work in ADO. It won't return the above record (and probably others). If I change the filter to 'brown' I get results in both ADO and Access. Why does it work on some records and not others?

[pre]
c_id c_name c_phone CountOfc_id
ALIBROWN BROWN,A(ALISTAIR) 0
[/pre]

 

[DotNetGnat]

you have WHERE, GROUP BY, HAVING all mixed up...

Try this:

SELECT tCaller.c_id, tCaller.c_name, 
tCaller.c_phone, Count(tTicket.c_fid) AS CountOfc_id 
FROM tCaller LEFT JOIN tTicket 
ON tCaller.c_id = tTicket.c_fid
WHERE ( tCaller.c_id <> '' AND 
(tCaller.c_id LIKE 'facey%' OR tCaller.c_phone LIKE '%facey%' OR tCaller.c_name LIKE 'facey%')) 
GROUP BY tCaller.c_id, tCaller.c_name, tCaller.c_phone 
ORDER BY c_name;

-DNG

 

[travisbrown]

Yeah, no, I realise that. Clumsy code, but should still work. That is the way Access writes it using the query grid on Aggregate queries. I used it as a last ditch attempt. Here is my original query with the same poor results: works in Access, not in ADO. I was tinkering around with inStr too, but it's not what I want to use.

SELECT tCaller.c_id, tCaller.c_name, tCaller.c_phone, Count(tTicket.c_fid) AS CountOfc_id 
FROM tCaller LEFT JOIN tTicket ON tCaller.c_id = tTicket.c_fid 
WHERE (((tCaller.c_id)<>'')) AND (((tCaller.c_id) ALike 'face%')) OR (((tCaller.c_name) ALike '%face%')) OR (((tCaller.c_phone) ALike '%face%')) 
GROUP BY tCaller.c_id, tCaller.c_name, tCaller.c_phone ORDER BY c_name;

 

[travisbrown]

I should note that the issue is futher confused by the fact that the DFACEY record above is returned by this query:

SELECT tCaller.c_id, tCaller.c_name, tCaller.c_phone, Count(tTicket.c_fid) AS CountOfc_id FROM tCaller LEFT JOIN tTicket ON tCaller.c_id = tTicket.c_fid WHERE (((tCaller.c_id)<>'')) AND (((tCaller.c_id) Like 'fa%')) OR (((tCaller.c_name) Like '%fa%')) OR (((tCaller.c_phone) Like '%fa%')) GROUP BY tCaller.c_id, tCaller.c_name, tCaller.c_phone ORDER BY c_name;

but not by this

SELECT tCaller.c_id, tCaller.c_name, tCaller.c_phone, Count(tTicket.c_fid) AS CountOfc_id FROM tCaller LEFT JOIN tTicket ON tCaller.c_id = tTicket.c_fid WHERE (((tCaller.c_id)<>'')) AND (((tCaller.c_id) Like 'fac%')) OR (((tCaller.c_name) Like '%fac%')) OR (((tCaller.c_phone) Like '%fac%')) GROUP BY tCaller.c_id, tCaller.c_name, tCaller.c_phone ORDER BY c_name;

Must have something to do with the bracket grouping, but I can't see where, and if so, why does it work in Access?

 

[travisbrown]

All fixed. Got the sysadmin to reinstall the Jet Driver as a last ditch effort. Works now. Go figure.