Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Crosstab reports (Access 2003 SP2)

    I have a crosstab report that prints 4 fixed columns, up to 30 variable columns followed by a totals column.

    What I have found is that the totals column is blank once the number of variables exceeds 20. However the grouping shows it's total ok.

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

    Re: Crosstab reports (Access 2003 SP2)

    C'mon Pat, you'll have to provide detailed information or a stripped down database.

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

    Re: Crosstab reports (Access 2003 SP2)

    It's not just the totals column, it's the detailed section columns as well.

    I will start to prune my database.

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

    Re: Crosstab reports (Access 2003 SP2)

    Sounds like something wrong with the code binding the columns to the crosstab fields. We'll never figure it out until you post the necessary. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

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

    Re: Crosstab reports (Access 2003 SP2)

    I have a cutdown version of the database ready but I need to check that it's ok with the client first.

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

    Re: Crosstab reports (Access 2003 SP2)

    I have condensed the database. If you run the form with it's default you will notice that Theatre has a totally blank column except for the grouping totals. Also the Totals column is blank except for the grouping totals.

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

    Re: Crosstab reports (Access 2003 SP2)

    The values get set correctly the first time, but then the Detail_Format event runs again with FormatCount still set at 1, and without a Retreat event in between, so the recordset is not synchronized with the current record in the report any more. At least, it seems that way. It's too late here to investigate further.

    I suggest that you take a look at the attached demo database (an Access 2000 format version of a database I attached some years ago in Access 97 format). It demonstrates a much cleaner way of handling a crosstab report with subtotals etc.

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

    Re: Crosstab reports (Access 2003 SP2)

    That database certainly does simplify the code, however, the report still will not print columns past column 20 in the detail section even with your coding.

    Help, is this a MS problem?

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

    Re: Crosstab reports (Access 2003 SP2)

    <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15> <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    The problem was very simple, but not easy to find: the Format property of Col21 through Col28 has been set to a space instead of being blank (empty). So any value was being displayed as a space. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>.

    Select Col21 through Col28 and clear the Format property. That should do it!

    (Working version attached)

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

    Re: Crosstab reports (Access 2003 SP2)

    Whoa. The man has x-ray vision, you know what that means folks, yes, he is superman.

    Seriously though, what put you onto that Hans? After all you could not see it (or could you <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)?

    Thank you Hans !!!!!!!!

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

    Re: Crosstab reports (Access 2003 SP2)

    I was getting desperate and started to compare the properties of Col20 and Col21, when I suddenly noticed that the insertion point in the Format property of Col21 wasn't flush with the left edge of the box. Bingo!

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

    Re: Crosstab reports (Access 2003 SP2)

    Another plus from all this, is the code you posted simplifies the crosstab report substantially.

    Thanks again.

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

    Re: Crosstab reports (Access 2003 SP2)

    It also opens much faster. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Crosstab reports (Access 2003 SP2)

    It's a pity that MS don't change this to ensure that the space is surrounded by quotes, that way I would not have annoyed you with such a dumb question.

    After all they "trim" everything else.

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

    Re: Crosstab reports (Access 2003 SP2)

    The question wasn't dumb, it was a real puzzler! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> But how did those spaces get there? <img src=/S/clown.gif border=0 alt=clown width=15 height=15>

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
  •