Number of displayed columns based on form data

[carroll42]

I have been having some problems trying to develop a query that will display certain columns based on what data is selected on a form.

My form [Sales Query] breaks out sales data into different columns based on the date it occurred (2003 sales, 2004 sales, etc) and only enters data into the column if the check box is selected on the form. If the check box is not selected it enters a zero value. The problem I have is that I don't want the columns to appear at all if the box is not checked.

Code:
SELECT [Report Name], Sum(IIf([Fiscal Year]="FY 1999" And [Type]="S" And Forms![Sales Query]!Check0=-1,[Value],0)) AS [FY99 Sales], Sum(IIf([Fiscal Year]="FY 2000" And [Type]="S" And Forms![Sales Query]!Check3=-1,[Value],0)) AS [FY00 Sales]
FROM [Combined Sales Table]
GROUP BY [Report Name];

Any help would be appreciated.

Thx

 

[PHV]

I suggest you build on the fly in VBA the needed SQL code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244