Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Freezes with certain Filter applied (A97 SR2)

    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

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report Freezes with certain Filter applied (A97 SR2)

    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.

  3. #3
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Freezes with certain Filter applied (A97 SR2)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Freezes with certain Filter applied (A97 SR2)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •