Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Question Unanswered: Dynamic Reports Using a Cross Tab Query

    Hi,

    This is perhaps a gross display of ignorance, but what to hell. I am doing reports (as an unpaid volunteer) for a small charity. They want to see all donations monthly, by campaign ( they have five or six campaigns of the same type each year, such as the "Spring Mail-out"), for a five year period. They would also like to see, overall, all donations month by month for five years. I want to use a combo box to allow them to select the particular campaign (or all), and to generate a report. I want to base all the reports on a single crosstab query and a single report design.

    Assumption: I don't believe that I can use a record filter in the actual report design to get only donations by a specific campaign type, as the field (campaign type) does not exist in the crosstab query output (am I wrong here?).

    So (and this seems a bit messy), I have attached a combo box to a function (sitting in a module), and placed the function in a "where" statement ("aCampaign()") in the query, and this actually works (the underlying crosstab query changes according to the selection in the combo box), and I get the report required. However It does not work if nothing (or the default) is selected (for the case of all campaigns together). I have tried returning "" in the function, but the function ("aCampaign()") seems to trigger the WHERE statement in the crosstab query, and returns no records (instead of all the records).

    I have thought about finding something like "ALL" or "*' to use when nothing (or a default) is selected with the combo box, but this doesn't seem to work either. Is there a reserved word usable with "WHERE" that allows for the selection of all records? It this aproach completely crazy? Any ideas?

    Regards

    J Smith
    Gatineau. QC

  2. #2
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Is Not Null ?

    Interesting, won't work in the query when I return "Not Is Null" via the function, but works when I enter it directly as a "WHERE" in the relevant field ("campaign_type").

    In VBA, is there a limit on functions calling functions (assuming that "IS NULL" is itself a function)? No idea why it does not work ( it seemed like a good idea)!

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This doesn't answer your question but there's a neat Crosstab example in the code bank (last post.) If you'd like to show your crosstab in form (to control events such as closing, exporting (you can easily export), etc) it's worth a look. It also has criteria fields on the form (but no criteria in the queries themselves in the example.)

    Sorry but I don't have an answer to your post right now. I'll try to answer later if someone else hasn't already answered.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You would need the campaign type as a row source or colomn source in your crosstab otherwise how would you know the campaign type for an ALL report.

    You could use a query to select the data as per your combo box for Campaign Type, I would also have boxes for a start month and year and another one for end month and year.

    You could then create a crosstab using the above query as the data source

  5. #5
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    A Little More Information..

    I have attached a screen shot of the query (you would not want to see the SQL). Essentially, campaign type information is in "source_id" (sorry, I changed names to make the original explanation easier), and "asource()" is simply a "getter" in a module(it retrieves a value set earlier via a combo box.) However, if I return "NOT IS NULL" via asource(), the query chooses no records (and I get nothing, even though this field is filled in every record). The approach (messy though it is) seems to work for individual annual campaigns (e.g. "Spring mail out"), but not for all campaigns combined (all).
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Not is Null?

    Shouldn't that be Is Not Null?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Red face More on This

    Its not a problem with "IS Null"; it works when I enter it directly in the query.
    I have tried all the variations, and I can NOt pass an anything that works through the setter/getter ( a function that passes data through the module)..

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    My bad, I didn't read this correctly the first time... or the second tbh.

    Firstly, your post talks about aCampaign(), but your screenshot shows aSource(). Do you have the wrong function perhaps? Just an obvious one, but it seems to be a conflict.

    I think it might be the way you are doing it. Why don't you just have your query refer directly to the combo box? That's what I always do.

    Anyway... what is the function doing when the combo box has "all" selected? It should be returning Like "*" or maybe "> 0 or < 1" or even "True" might work, not Null or a zero length string because the query itself runs the WHERE clause because the WHERE clause is not empty, it contains an expression. Since, because the way you are doing this, the returned string has to specify what you want explicitly.

    I don't believe that I can use a record filter in the actual report design to get only donations by a specific campaign type, as the field (campaign type) does not exist in the crosstab query output (am I wrong here?).
    No, you are correct. The criteria has to be in the query.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Thanks for the thoughts. I know the approach is messy (a cross breed betwen Java and trial-and-error): perhaps better to forward it through a combobox (I asume I would have to name it something like form_aForm.aCombobx.value) and that this can be placed directly in a "where" portion of the query). The mystery to me, still, is why "not is null" works fine when entered directly in the query, but cannot be passed through a function (which otherwise works fine in the selection of specific criteria).

    Regards


    John S

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Perhaps because when you enter it directly, you get Not "Is Null" and when it's done programmatically, it feeds "Not Is Null" ??
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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