Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report criteria based on formulas (2002 SP-2)

    Hi,

    More trouble in paradise. I would like to limit the results in a report. The value I would like to base that upon is in a running total in the detail (hidden) and again in the report footer (visible). Is there any way to require a certain value in one of those fields?

    I tried doing the limit function in the query call, but since the function is running a total of a field - I can't have aggregate functions in the WHERE clause. That's what led me to the report. The count I'm looking for exists in the report. Since I have that data, is there any way to base the generation of the report on values in that field?

    Thanks again for all your guruistic help!
    Y'all rock!

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

    Re: Report criteria based on formulas (2002 SP-2)

    Do you want the report to display all records up to the moment a specified maximum value for the running sum is reached? That could be done with some VBA code in the report itself. Or do you want to display ONLY the record where a specified value for the running sum is reached? You would need to handle that in a query, for the moment you display only one record in the report, the running sum isn't a real running sum any more!
    I don't see how you could use the grand total in the report footer for something similar.

  3. #3
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report criteria based on formulas (2002 SP-2)

    Thanks Hans!

    I had a feeling you were gonna say that. ;- Yeah, I was hoping to be able to say "Any groups with less than 5 records, stay out!" But that just won't fly, will it. I'll work on the query idea & let you know how it turns out.

    As always,
    Greatful to have you here!

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

    Re: Report criteria based on formulas (2002 SP-2)

    "Any groups with less than 5 records" does not refer to a running sum, but to a group count. You could do this in the report as follows:
    - In the group header, place a text box with control source =Count(*). Call it txtCount. It can be invisible.
    - Create an On Format handler for the group header:

    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    Const intLimit = 5
    Cancel = (Me.txtCount < intLimit)
    Me.Detail.Visible = (Me.txtCount > intLimit - 1)
    End Sub

  5. #5
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report criteria based on formulas (2002 SP-2)

    I guess I was using the vernacular a little too much in my last posting. I'm not Grouping, using group headers, footers, etc.
    I have 2 sub-reports and a total count of the records in both sub-reports in my main report detail.

    Using the event proceedure you described, I'm trying to come up with a way to use the value of the totals text box to decide whether the Detail of the Report is visible. I think the problem I'm running into, though, is that the value of that text box doesn't get generated until the Detail is formatted, right? The same would go for the value the totals text box is computing.

    So it looks like I'm back to the multiple query idea. Which is a good one. But you know how these projects are, once they ask for one thing, then the change what they want.

    Thanks again for all your help. I'm actually going to try to get some sleep now & tackle all this in the AM.

    Y'all r tha best!

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

    Re: Report criteria based on formulas (2002 SP-2)

    The record count from the subreports is not available before they have been populated, so you'll have to get the count another way. Queries are one possibility, calculating the counts using DCount is another way.

  7. #7
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report criteria based on formulas (2002 SP-2)

    Ok - I'm starting to get gloomy. :-( hehe.

    DCount and Queries are basically giving me the same problem. How would I see the results of a dcount for just one record? I can't seem to limit it to just that value. dcount wants to count the entire column's worth of data instead of the individual record.

    So I guess my question now is: How do I specify the current record in the criteria? Ex:

    BCNT: DCount ( "[bnf] ", "[2K3Q1]",

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

    Re: Report criteria based on formulas (2002 SP-2)

    The DCount of a single record is by definition 1, but I assume I'm missing something here.

  9. #9
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report criteria based on formulas (2002 SP-2)

    Yeh - I'm missing things too! ;-]

    I'm looking for all instances of "bnf" in table "2K3Q1". "Bnf" also changes with every record in it's table. I'd like to limit the results of my dcount to just the value in "bnf" for the current record, but I don't see any examples use this. All I've seen is using some kind of static string for the criterion.

    Currently my results are like:

    bnf DCount in 2K3Q1
    Joe Smith 33261
    Sue Black 33261
    Bill Gomez 33261

    What I'd like to get is:

    bnf DCount in 2K3Q1
    Joe Smith 13
    Sue Black 5
    Bill Gomez 27

    I'm starting to think I may have to go VBA on this and create a variable for the criteria that is based on each value of "bnf" in it's table and return the results in a new field. Unless there's a way of doing that just within the dcount in the expression builder, that's the way I'm leaning.

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

    Re: Report criteria based on formulas (2002 SP-2)

    Try this (just a wild guess)

    BCNT: DCount ( "*", "[2K3Q1]", "bnf = " & Chr(34) & [bnf] & Chr(34))

    I assumed that bnf is a text field. If it is numeric, omit the Chr(34)s (they are quotes):

    BCNT: DCount ( "*", "[2K3Q1]", "bnf = " & [bnf])

  11. #11
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report criteria based on formulas (2002 SP-2)

    Wow! That's funky. Ya had to go ASCII on me, huh?

    I think that's going to solve my problem. You've saved me a lot of programming on the vba side! Thank you.

    If I may ask a follow up question: Why is the Expression a wildcard in this case?
    I thought you'd need to specify the field you we're counting.

    Also, Woody's Lounge and you in particular have helped me out numerous times. Is there a link or something on the main page where I could donate to the site, maybe even specifying your ID?

    Thanks for being there!

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

    Re: Report criteria based on formulas (2002 SP-2)

    If you specify a field name as first argument for DCount, all records with a non-null value in that field and that satisfy the criteria are counted.
    If you specify * as first argument, ALL records that satisfy the criteria are counted.
    See the online help for DCount (click in the word DCount in the VB Editor and press F1)

    <img src=/w3timages/blueline.gif width=33% height=2>

    Woody's Lounge is run entirely by volunteers, we don't accept payment. You can, however, click on the Google ads in the lower left corner of most Lounge pages. The (very small amount of) money generated by this contributes to the upkeep of the Lounge server.

Posting Permissions

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