Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Location
    Colorado
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting specific records in reports.. (Access 97)

    In Access 97 I have designed a report which includes four columns that display data from a table as a Yes/No checkbox (the data refers to whether a certain employee belongs to a certain group as in "Executive", "Marketing", "IT Department", etc.) What I have done is to add code to the Detail_Format event for the report which checks each record as it is formatted to see if the check-box is checked for that group. If it is, the checkbox is displayed, if not the checkbox is made invisible. In this way I have a report where the checkboxes show up only if they are checked and you don't have a whole lot of blank boxes littering the form.

    Now, what I would also like to do is count the number of records for each column for which the check box is checked. I made a pass at it by writing code, also in the Detail_Format event, using globally declared integers. As each record is formatted, if the checkbox for that particular column is checked, the variable for that particular field in incremented. At the end of formatting, the final integer value is put into an unbound text box at the top of each specific column.

    Here's the problem -- if I just print the report, the numbers come out accurately. If I open the report to review it, then the numbers also come out accurately. But if I open the report first to view it and *then* print it the numbers are screwy. There is a pattern, however, as the numbers seem to be incremented by one or two integers, not on every column but randomly on different columns. My code includes the FomatCount() function to check which page the report is on, and as I say, it works fine when either straight printing or straight viewing. It's only when I try to view and then print that things go haywire.

    Is there something I'm missing about the procedures for report printing? Any help wouold be greatly appreciated!! Thanks in advance.

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

    Re: Counting specific records in reports.. (Access 97)

    What I would do is to change the underlying query to include an extra field for each checkbox that does the following:
    ChkBox1: IIf(CheckBox,1,0)
    This will return a 1 if Checkbox is true.
    You can then introduce (invisible) fields on the report in the detail section and use a Sum in the Report Footer to give you the totals you require.
    If you don't understand something I have written here please post.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Counting specific records in reports.. (Access 97)

    I suspect that your globally declared variables are not being reset in all cases. A further issue is that the Detail_Format event can occasionally be called recursively, causing more inaccuracies. I would eliminate the counting code, and use a trick to get the counts. The trick depends on the fact that False checkmarks are 0 and True checkmarks are -1 (if you are dealing with Jet records). So you can simply Sum the checkboxes and take the absolute value to get the count of boxes checked. Hope this makes sense. Off topic, welcome to another Coloradoan.
    Wendell

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

    Re: Counting specific records in reports.. (Access 97)

    Nice trick but dangerous if you move the database to SQL where the returned values of True and False are different.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Counting specific records in reports.. (Access 97)

    That's why you sum the absolute value of checkbox fields, as Wendell mentions. You'd use the VB ABS function, which returns the absolute value of a number, for this purpose. The absolute value of a number is its unsigned magnitude. For example, ABS(-1) and ABS(1) both return 1. Example: Add unbound textbox to report footer and set its ControlSource to:

    =Abs(Sum([FIELD1]))

    Where FIELD1 is a Yes/No (checkbox) field in report.

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

    Re: Counting specific records in reports.. (Access 97)

    That makes it absolutely clear <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

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

    Re: Counting specific records in reports.. (Access 97)

    Yes I know about the abs function, however I was just pointing out that Access uses -1 as the value of True and 0 for False whereas SQL Server uses other values (I think, I'm not abssolutely sure about this?). <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    If this is the case you will have problems moving to SQL Server in the future.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Counting specific records in reports.. (Access 97)

    False is 0 in Access and in SQL Server and also in most programming languages; True is -1 in Access and +1 in SQL Server, and I don't know of other values used in any system. So using Abs should be sufficient to guarantee that things will work as expected, whatever the platform.

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

    Re: Counting specific records in reports.. (Access 97)

    Fair enough, I hope they don't change it to -2 though, else ...
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Counting specific records in reports.. (Access 97)

    To add absolutely nothing more to this discussion, the only way the wheels can fall off using this trick is if you decide to sum a field from an MDB database and another field from a SQL Server table. Then you would get garbage results.
    Wendell

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

    Re: Counting specific records in reports.. (Access 97)

    Or

    =Sum(Abs([Field1]))
    Charlotte

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Counting specific records in reports.. (Access 97)

    You'd be advised to please ABSstain from any further "puns" in this forum; save any witty remarks for "Scuttlebutt".... <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

  13. #13
    New Lounger
    Join Date
    Feb 2002
    Location
    Colorado
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting specific records in reports.. (Access 97)

    Hi WendellB:

    Thanks for the tip -- it works like a charm! I don't know why I didn't think of that myself. I guess I've been way too deep into the code for this DB for so long that I couldn't see the forest for the trees.

    And to the other posters who mentioned porting this over to SQL and having potential problems -- first, if I did, I think it wouldn't matter since the absolute value is the absolute value, whether +1 or -1 is used; second, I don't think it's gonna happen anyway. The company I did this for is a small one and they're currently trying to compile the funds to get a real actual server (they're on a peer-to-peer network right now) so I don't think they're going to do anything regarding SQL server at this time.

    Thanks again to all who responded.

    >>TimK

Posting Permissions

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