Results 1 to 5 of 5
  1. #1
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314

    Is there Generic VBA any code to create a dynamic cross tab report from a cross tab query


    I need to create a dynamic report using a cross tab query. Apparently this is a major VBA task, and I am not good at VBA. It amazes me that MS has not created a report wizard to do this task. Does anyone know of anyone who has created a generic cross tab report which will work with most any cross tab query?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont need vb. Build the crosstab query using the Query Wizard.
    Then just run the query.
    As for reports, the vb option would be too complex for a beginner to decipher, so instead,
    make a 'report table' of the crosstab query. The fields will be of every possible option the query can produce.
    make a report using this table, then make an append query to add the data from the crosstab query.
    If the field exists, it gets added, if not, its ignored.

  3. #3
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    I have the cross-tab query working just fine. It produces results for a specified year. The pivot field happens to be an event date. Since the dates change every year and the events may vary in number and be recorded at different intervals, it would lead to an infinite number of possibilities.

    I have seen where people have offered VBA code for producing a cross-tab report for certain instances. But it would be difficult for me to adapt them. So, I was hoping someone may have done what MS should have done in the first place, and written a generic VBA module, which would just need to be told the name of the cross-tab query to work. Maybe that is too much to ask for. Most of the solutions involve creating a form to hold a control button to execute the code. I wouldn't mind jumping through those hoops. The cross-tab query produces in the neighborhood of 20, or so pivot columns. The exact number differs each year.

    All I can do now, is run the query and save a screen snippet as a jpg to distribute.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    for crosstabs, i only deliver spreadsheets.
    since reports are too difficult to manage for Xtabs.

  5. #5
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    That would be a good alternative. How do I export the Xtab query to Excel.

    Never mind. That was too easy. Thanks for the suggestion.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cross-Tab Report?
    By WCStarks in forum Reports
    Replies: 12
    Last Post: 08-14-2017, 03:14 PM
  2. Pass Parameters from Cross Tab Query to Report
    By jokeboy1 in forum Reports
    Replies: 5
    Last Post: 01-02-2015, 01:26 PM
  3. Cross tab report
    By wnicole in forum Reports
    Replies: 2
    Last Post: 10-09-2013, 05:07 AM
  4. Report is cross printing
    By wildthingcg in forum Reports
    Replies: 6
    Last Post: 01-04-2013, 10:30 AM
  5. Adding rows on cross-tab query report
    By KahluaFawn in forum Reports
    Replies: 2
    Last Post: 02-18-2009, 10:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums