Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query runs, but not report - help! ('97)

    I have a couple of Access'97 databases and up until now, the reports ran just fine. All of a sudden the reports, which all use a date field as their filter, have gone nuts. However the queries which gets the data for the reportsall work just fine. Each of the reports starts OK, but then the bar in the lower left hand corner says 'Formatting page. Press ctrl+Break to stop.'
    One report runs fine if the filter is between 1/1/02 and 1/16/02, but not if the date is after 1/17/02. I even rebuilt the report from scratch - no luck. The other one ran last night using 2001 as the filter, but not today.
    Does anyone have any idea what's up? Is this a date issue with Microsoft?
    If anyone can explain/ fix this, it's you folks who know so much. Thank you. Judy

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query runs, but not report - help! ('97)

    I suggest that you always use the full year rather then the last two digits. This article may help shed some light...

    <A target="_blank" HREF=http://support.microsoft.com/default.aspx?scid=kb;en-us;Q208339>http://support.microsoft.com/default.aspx?...b;en-us;Q208339</A>

    hth,
    Jack

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query runs, but not report - help! ('97)

    Thnaks for the suggestion. Just to make sure, I went back to all date fields and changed them to YYYY. Since we all are using Win'98 I did not think this would make a difference, and it didn't. Even with all dates having 4 digit years, and all filters using 4 digit years, I still can't run these.
    I've run them monthly without problem up until now. The last run was in January, using dates through December 2001. This is the first run using dates in 2002. Is there anything unique about 2002?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query runs, but not report - help! ('97)

    Are you saying that the *same* query ran one day and not the next using the same criteria? In that case, did the data change between days? Have you compacted the database? If you have a split FE/BE, do you have a prior backup you could restore and test? Access 97 was not Y2K compliant, so I wonder if you're running into something related to that.
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query runs, but not report - help! ('97)

    A couple of possibilities, but how does the report fail? Does it just lock up? Or does it give you some sort of error message? Or does Access just crash and go away?

    As far as I know, there isn't anything special about 2002 - it isn't a leap year or anything. It is possible that you have some sort of filter in the reports which is blocking you from running past a certain date, but I suspect it's more likely that your report has managed to corrupt itself, or you have corrupt data in one of your tables. I would suggest importing everything into a new database and see if that fixes the problem. Hope this helps.
    Wendell

  6. #6
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query runs, but not report - help! ('97)

    I ran reports in early January, using December dates. I had no problem then. The db is not split.
    I can run a report with dates between 1/1/2002 and 1/16/2002. Any query that goes past the 16th will run as a query, but not as a report. I'm totally lost!

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query runs, but not report - help! ('97)

    Thanks. I was hoping to avoid that (importing piece by piece into a clean db). I'd run to do that EXCEPT I am having the same problem with reports in a 2nd database, totally unconnected to the first, stored on a different server (although accessed through the same LAN), whether I run it on my version at home or work.
    Everything is fine in the 1st one unless the date in the criteria is after 1/16/2002. In the other database, the criteria is the 4 digit year in the short-date field for enddate.
    In both cases, the query runs just fine but the report built from only that query stalls. In the lower right hand corner, where the >>>>>>> shows the progress of the report being generated, the message 'Formatting page, press ctrl_break to stop' shows up, the progress bar goes to the end, and it just stops right there.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query runs, but not report - help! ('97)

    That does seem strange. Charlotte is correct in that Access 97 is not completely Y2K compliant, but I don't recall issues with any dates before about 2007. In addition, if the queries run and return results, it would seem that the report must be the culprit. The only idea I can suggest right now is to try creating a simple report using the queries and see if it works correctly. (Sorry - I just reread your first post and see that you did try to recreate the report.) If it does, that would suggest that your report is corrupted - an unfortunate fact of life where databases are concerned. I'll chew on your problem and see if any other ideas pop up.
    Wendell

  9. #9
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query runs, but not report - help! ('97)

    I took out all but the table (and deleted most of the entries), query and report- still have the same problem for the one filtering for year. I'd attach it, but even at that minimal level it is 1.06mg.

    I'll keep my fingers crossed that a lightbulb goes off for someone. Thanks for your help.
    Judy

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query runs, but not report - help! ('97)

    Is this a multi-user database? If so, can you get the report to run on another PC? I had a problem this morning where a database worked on two machines but didn't on the third. It had some sort of DLL problem that prevented the Switchboard code from working. Go figure! Are you are setting the filter on the report through code? If so, post the code and maybe something will jump out from that. You might also post the data source for the report. I presume it has subreports - if so posting their code might help.

    On another track, have you checked the number of records being returned by you query? If it's very large, you may be running out of resources on your PC. In that case the report may simply be waiting for all the data to be returned. Seems like a long-shot, but you never know. Every once in a while, I manage to get a join going that returns every combination of records from two tables - a few hundred in each will give you LOTS of records. That doesn't seem likely based on the date behavior you describe, but ....
    Wendell

  11. #11
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query runs, but not report - help! ('97)

    Yes, it is a multi-user db. However, tonight I used the copy I have on my computer at home (I am the administrator of the file) to make a copy with only the 3 pieces, and it still has a problem.

    No, there are no sub-reports. 1 table, 1 query, 1 report- very straight forward. The filter is only the query itself- no separate filter. And at this point I have only 10-15 rows in the table, so it's not a question of too big a file. Now that may be the case on the other db since it now holds over 10,000 records. Is there something I should be doing to protect that one against its size?

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query runs, but not report - help! ('97)

    This gets curiouser and curiouser! Can you email me the simple database you tested at home? You can find my email address on my profile. (You could attach it to a post too - your preference.) I would suggest you ZIP it with WINZIP or one of the other compression tools. An Access database with three objects and 15 records should compact to less than 100Kbytes. I'll try to test it on both Access97 and 2000, but it won't be until late today - gotta go to work for a change.
    Wendell

  13. #13
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query runs, but not report - help! ('97)

    My 2 cents worth
    In control panel under regional settings make sure your short date format is set to MM/dd/yyyy
    I had some date issues when the setting was at the default which I think was M/d/yy

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query runs, but not report - help! ('97)

    I meant to answer your question about importing this morning, but forgot, so I'll do it here. The process is
    <UL><LI>Create a new blank database
    <LI>Do the File/Get External Data/Import
    <LI>Select the old database that you want to clean and compact
    <LI>Click the Select All button on the tables tab
    <LI>Select the Queries tab and click the Select All button
    <LI>Do the same for the other 4 or 5 tabs (97 has 4, 2000/02 have 5)
    <LI>If you have import specifications or other unusual needs, click the Options button and select appropriately
    <LI>Click the OK button and watch Access do its thing[/list]If you go back to the original database and try to do it as an export, you are correct. It will only let you do one thing at a time. Sometimes if a database is corrupted, that is the only way to get some objects into a new database, but the import works much better. Hope this makes sense.
    Wendell

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query runs, but not report - help! ('97)

    A footnote to this thread -
    It turns out that both databases were experiencing the same problem - the second one also contained a couple of records that someone had pasted pieces from a Word document or some other app, and it also caused the report format to go to sleep. One record appeared to have a Word field setting the current date. I should also note that this same behavior occurs in Access 97, 2000 and 2002. It turns out that Judy was able to identify the user who had entered this data, so some user training <img src=/S/bash.gif border=0 alt=bash width=35 height=39> will solve the immediate problem until the next clever soul comes along. I'm actually somewhat surprised that I haven't seen this before, knowing some of the users I've encountered.

    The only longer term solution I've been able to think of (aside from a Microsoft fix) would be to actually scan the memo field for any non-printing characters. I suppose a rich text control might also solve the problem. Any other ideas out there????
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

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