Query runs, but not report – help! (’97)

Topic

New Reply

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

Reply | Quote

Replies

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

http://support.microsoft.com/default.aspx?…b;en-us;Q208339

hth,
Jack

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 ….

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

I meant to answer your question about importing this morning, but forgot, so I’ll do it here. The process is

• Create a new blank database
• Do the File/Get External Data/Import
• Select the old database that you want to clean and compact
• Click the Select All button on the tables tab
• Select the Queries tab and click the Select All button
• Do the same for the other 4 or 5 tabs (97 has 4, 2000/02 have 5)
• If you have import specifications or other unusual needs, click the Options button and select appropriately
• 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.

Reply | Quote

Problems solved! SORT OF – see below!
Original post on 7-Feb-02
It turns out that Access doesn’t much like tab characters in a memo field. It appears that text from Word or another text editor had been pasted into one of the memo fields and that was fine as they showed up as non-printing characters (little boxes) in datasheet mode. But when you tried to preview a report, Access 97 didn’t like them at all and went into a permanent formatting mode. You could break it with a CTRL-Break, but it would never display any data. It’s a problem I’ve not seen before, and I didn’t try testing Access 2000, but I will try to do that in the near future. The second database problem referred to in this thread was unrelated.

modified on 17-Feb-02 to correct the post and make it more searchable
Several things in the original post are not accurate – the formating of reports in two different databases did hang, and you had to CTRL-Break to get Access to respond again. However the reason was that the memo fields contained enough data to cause the report to completely fill one page and try to start another page. BUT, the detail section of the report was set with Keep Together to YES. It turns out that deleting the tab characters made the field enough smaller to just fit on one page. ARGH! Just when you think you’ve got it all figured out, something like this comes along to humble you. Anyhow the behavior is consistent across all three versions of Access. To fix the problem, we simply set the property to NO.

Reply | Quote

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 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????

Reply | Quote

I think the problem may be *specific* non-printing characters, Wendell. I have created forms in the past with a textbox that allowed users to actually enter tabs into a memo field as they typed in the text. That memo field exported properly and the data could be imported back into an Access database with no problems.

Reply | Quote

I think you may be correct – I haven’t done an actual scan of the data, so it could well be something other than the tab characters – they displayed as non-printing characters in Access in table and query views, but when you pasted them into Word they displayed as tab characters. Everything other than the reports seemed to be fine, but the report formatting went south when that record was included in the data source. I’ll do a bit more detective work and see what I find.

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote