Prev: Link Unicode File
Next: 2003: Table Field Type For Beeeeg Decimal Numbers?
From: The Frog on 13 Aug 2010 06:31
Okey Dokey,

Firstly let me say a big Thankyou. The problems are now fixed.

Here is what went wrong:
1/ Database corruption

When I slurped all the db objects and data into a new blank db I
started to get some normal looking errors :-) This started to make
sense as I can work with an error and correct the fault.

- The combobox being used as a filter for the underlying queries
actually now works as expected. No like clause needed, just set the
where to be the value in the combobox and hey presto - normality. No
more fiddling around. And here was I thinking it was some weird date
related thing and data type conversion issues.

The label with the mysterious correct dates showing up magically
started to produce.....nothing! That is what it should have been
producing all along. I wrote a simple piece of code for the report to
populate the labels caption with the correct information and hey
presto - normality. I was sure I was seeing things before.....

The blank pages in the main report. Now I was getting an error when
running the main report that made sense. You cant have non fixed
columns with either a crosstab or passthrough query in a sub report. I
know this one. Solved it by breaking the problem into two. First I use
the users selection to build a temp table where the data for the
reports will live. Second, since the data now lives in a temp table I
changed the reports data selection queries from their original 'query
as table' input to the temp table as the data source. Once that was
done it was easy to test each crosstab based on its filtering query
(actually there are loads of calculated junk in there that a crosstab
just wont allow you to do - so it has to be pre-aggregated for this to
work), and of course remove the parameters from the crosstabs design.
This meant that as far as Access is concerned the non fixed columns
problem thingy goes away. Yeah! The crosstab runs, the report based on
it runs, and now that there are no parameters being passed through the
main report runs. Hey presto - normality!

I think now I am going to have a beer, or ten.......

Thankyou all for your pointers you got me looking in the right
directions. I thought I'd share the solution as it was really out of
the park for this one. Talk about ghost in the database........

Cheers

The Frog
First  |  Prev  | 
Pages: 1 2
Prev: Link Unicode File
Next: 2003: Table Field Type For Beeeeg Decimal Numbers?