Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 26th, 2008, 10:16 PM
Registered User
Join Date: Mar 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Report from Query

Summary: the user is able to control (include or exclude in the output) 35 checkbox / fields in the query. Currently output is going to an excel sheet. Goal create a dynamic changing report.

Dear All,
This is a bit complicated but I hope that you would be able to help me out.
I have created a query off a linked table (from AS/400). This query has several fields that are controlled from a form using checkboxs, where if the check box is checked then the field will show up in the sql statement if not then then sql will run without that field. Currently I'm having the spreadsheet output to an excel sheet.
But what I'm looking (which is my optimum goal) to have a report based on that query that would dynamically include whatever fields are checked and remove whatever not checked.
Now I thought about this and I'm thinking of creating a report based on the query that would include ALL the fields then I insert some code that should be run- when the report is activated or being populated - that would go and check all the check boxes then what ever is not checked then the code will make the attribute of that field in the report of Visible as false thus removing it from the report...
Is my solution possible? is there such a code that would be created? I would appreciate if you can help me out with this...

Any idea/opinion is greatly appreciated...
Thank you,

Old March 28th, 2008, 06:41 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

It looks to me that what you are asking for, while inconsistent (is this an Excel spreadsheet or an Access report?) can be done with the Access report.

Create a module, and then within the module, create 35 public variables As Boolean:

Public bCheck1 As Boolean
Public bCheck2 As Boolean
... etc

Then on the form that launches the report, set the value of the boolean based on the check box state:

If Me.Check1.Value = True Then
   bCheck1 = True
   bCheck1 = False
End If

If Me.Check2.Value = True Then
   bCheck2 = True
   bCheck2 = False
End If
... etc

Then on the On Format event of the Report's Detail section, do this:

Me.Column1.Visible = bCheck1
Me.Column2.Visible = bCheck2
... etc

This will set the Visible property of each column to either True or False, depending on whether the checkbox was checked or not.

If you wanted to be more rigorous, you could make sure that the default value of each check box is set to false.

Did that help? Does that resolve all your issues, or do you still have the export to Excel issue?


Look it up at: http://wrox.books24x7.com

Similar Threads
Thread Thread Starter Forum Replies Last Post
Report Query happygv SQL Server 2000 4 June 11th, 2007 08:16 PM
Crystal Report Query asters Crystal Reports 0 December 29th, 2006 10:37 AM
Non query Report Parameter qa BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 July 2nd, 2004 02:49 PM
Generating a report from more than one query lic023 Access VBA 1 March 31st, 2004 06:46 PM
Report query khautinh SQL Server 2000 2 July 22nd, 2003 02:21 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.