Access query corrupts depending on # of records ?? 1

[gmTater]

I run the following query from ASP or Access and it works, with no problems. 51 records are returned.

SELECT Problems.Detail, ProbID, Count(*) AS ProbCnt
FROM Problems, Logs
WHERE Logs.Detail=Problems.ProbID And Open_Date>=#5/1/2004# And Open_Date<#6/1/2004#
GROUP BY ProbID, Problems.Detail;

But if I add one more criteria to the where clause, the Problems.Detail column corrupts. Only 2 non text characters appear instead.

SELECT Problems.Detail, ProbID, Count(*) AS ProbCnt
FROM Problems, Logs
WHERE Logs.Detail=Problems.ProbID And Open_Date>=#5/1/2004# And Open_Date<#6/1/2004# And Svc='E'
GROUP BY ProbID, Problems.Detail;

The table Logs has around 255,000 records. If I delete some records and bring it down to around 50,000 the 2nd query starts working. Is there possibly another way to write this?

 

[PHV]

Have you tried this ?
SELECT Problems.Detail, ProbID, Count(*) AS ProbCnt
FROM Problems INNER JOIN Logs ON ProbID=Logs.Detail
WHERE Open_Date>=#5/1/2004# And Open_Date<#6/1/2004# And Svc='E'
GROUP BY ProbID, Problems.Detail;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gmTater]

Thanks for the idea, I hoped there might be another way to try, but Access still corrupts the column. Problems.Detail is a memo field. I stumbled on it by accident, but if I change it to a text field the problem goes away. This is the 1st time I've come across the quirks in Acces that I read about.