Report Freezes with certain Filter applied (A97 SR2)

Topic

New Reply

I have a report based on a query which has no criteria to filter on. Instead, the user makes all the criteria selections on a form. When the user clicks the Preview button on the form, an SQL string is built and passed as the strLinkCriteria argument of the OpenReport command. It has been working fine for months…but then! All of a sudden, one criteria value started causing the report to freeze while it is formatting. It seems to go into an endless loop somewhere. The Status bar indicates the the query has run and the report is formatted, but when the Progress bar reaches 100%, it stops there! I have to press Ctl + Break to break out.

It so happens that the strLinkCriteria string looks like this when it fails:
[Sub_CA ]= ‘AR’ That’s it! Nothing earth-shattering in that string! No other criteria selected.

Oh, by the way, this DOES work… [Sub_CA ]= ‘CT’
And so does this: [Sub_CA ]= ‘cks’, and so on…
Only the initials ‘AR’ seem to cause a problem.

I can run the underlying query the ‘AR’ criteria entered and it returns 46 records.
If I save the query with that criteria, and run the report manually, (without the filter), it still fails. Yet, I CAN do these same steps with other initials as criteria and it works fine.

More information:
-The [Sub_CA] field is a hidden control on the report
-It is located in one of 4 group headers
-It is NOT the field being grouped by

I can’t seem to find anything different between the records that have ‘AR’ values and all other values.

I look forward to an enlightening, an probably Humbling, solution. (Other than having to fire this girl [A.R.])

While I am waiting, I will begin reading my Access for Dummies book again. Must have missed a paragraph….

Thanks,

Rich P

Reply | Quote

Replies

I presume [Sub_CA] is a field in the underlying query of the report?
Is the table in the query a local table, linked or an ODBC table?
Would you post the query here with the criteria (‘AR’) in it.

Reply | Quote

This is a split database, and all the tables are linked to a backend file. All users have the front-end file on their own machines. Nothing trickey here.

One point that may be worth noting is that many of us connect to this database through Citrix. We are spread out over the East Coast and mid-Atlantic states while the files reside in Florida. (But this has worked fine in the past!)

The query SQL view looks like this…

SELECT DISTINCTROW tblALL_Owners.Client_ID, tblALL_Clients.Client_Name, tblALL_Facilities.Fac_ID, tblALL_Facilities.OWN_ID, tblALL_Facilities.Fac_Name, tblALL_Facilities.FAC_ADD, tblALL_Facilities.Fac_City, tblALL_Facilities.Fac_St, tblALL_Submittals.Sub_Num, tblALL_Submittals.Sub_Amt_Pending, tblALL_Submittals.Sub_Status, tblALL_Submittals.Sub_Date_Due, tblALL_Submittals.DateReviewed, tblALL_Submittals.Sub_Work_Per, tblALL_Submittals.Sub_Comment, tblALL_Submittals.ESS_Job, ProgMgrs.Staff_Long AS PM, ClaimAnalysts.Staff_Long AS CA, [PM] & “/” & [CA] AS [PM/CA], tblALL_Submittals.Sub_Res_Staff, qryALL_Claims_Pending_Totals.SumOfInv_Paid_Amt, tblALL_Submittals.DateSubmitted_Client, tblALL_Submittals.Sub_CA
FROM (tblALL_Clients INNER JOIN tblALL_Owners ON tblALL_Clients.Client_ID = tblALL_Owners.Client_ID) INNER JOIN (tblALL_Facilities INNER JOIN ((tblALL_Staff AS ProgMgrs RIGHT JOIN (tblALL_Submittals LEFT JOIN qryALL_Claims_Pending_Totals ON tblALL_Submittals.ESS_SUB_Key = qryALL_Claims_Pending_Totals.ESS_SUB_Key) ON ProgMgrs.Staff = tblALL_Submittals.Sub_Res_Staff) LEFT JOIN tblALL_Staff AS ClaimAnalysts ON tblALL_Submittals.Sub_CA = ClaimAnalysts.Staff) ON tblALL_Facilities.Fac_ID = tblALL_Submittals.Fac_ID) ON tblALL_Owners.Own_ID = tblALL_Facilities.OWN_ID
WHERE (((tblALL_Submittals.Sub_Status)=”AF-In Prog” Or (tblALL_Submittals.Sub_Status)=”DP-in prog” Or (tblALL_Submittals.Sub_Status)=”In Progress”) AND (Not (tblALL_Submittals.Sub_Date_Due) Is Null));

I should mention that I did get this to work yesterday. But I am the first to admit that I can’t explain WHY it works. I literally created an empty report object, then copied and pasted the contents of the original report into the new report and it worked fine! Could be that there was some corruption in the report object. Of there was a setting that I missed that should have been different.

I would love to know what went wrong. But I was able to at least get the customer going again.

If you can see anything wrong with the query, please let me know.

Thanks for the reply.

Rich P.

Reply | Quote

From your description, it may well be that the report got corrupted. Unfortunately, such things happen from time to time, and it is very difficult to discover the cause, if it can be found at all.

It’s a good idea to keep a backup of a working version of the frontend. Often it’s easier to restore a backup than to try to repair things.

Reply | Quote