Is it possible to have a form designate which FIELDS to include in a selection query? I know that I can determine criteria and such, but i want the user to be able to generate a report based on the query select a, b, c from blah, or if they wish, just select a, b from blah...

Kix,

Definitely attainable ... but worth it?

If you programatically generate your queries, you can do this.

You could easily add a bunch of unbound checkboxes next to your fields.

Prior to running the query, generate the SQL for it by seeing which textboxes
are checked.

But then, you need to have a report that can accomodate a variable number of
fields (and datatypes), as well with any running sums, sorting, etc.

Unless you really have a requirement to do this, I'd look for an alternative.

just some thoughts,
Wayne

Hi Wayne,

Thanks for the heads up. I'm pretty sure it wouldn't be terribly complicated due to the fact that the only fields that woudl be dynamic are named Jan07, Feb07, Mar07... etc, each with the exact same kind of data. I was planning on having the user select the months using a form. So I guess my real question is, how do I get an SQL string I generate with VBA into the query on which my report is based?

Kix,

First, repeating fields are generally a bad thing. The monthly #s should go into a
child table. Just had to say that.

To make a query:


Dim qdf as QueryDef
Dim strSQL As String

Set qdf = CurrentDb.OpenQueryDefs("qryYourQuery")
'
' build the query ... very simplistically
'
strSQL = "Select "
If Me.CheckBox1 Then
strSQL = strSQL & "Jan07, "
End If
If Me.CheckBox2 Then
strSQL = strSQL & "Feb07, "
End If
'
strSQL = "From YourTable"
'
qdf.SQL = strSQL ' <-- Save the SQL
qdf.Close


Actually, the above code won't generate the exact SQL that you need, it's
just a starting point. But it is a solution.

The real solution is to break the monthly data into a child table and let
Access process it without any code. You WILL revisit this issue next year
and the year after, ...

Might as well get it designed properly from the start.

Wayne

What do you mean, a child table?

Kix,

Say that you have the simple task of finding which of your entities have
ever had a monthly amount > 1,000.

That sounds simple enough. There are two flavors of this.

Normalized:


Select Master.EntityName, Child.PaymentDate, Child.MonthlyAmount
From Master Inner Join Child on
Master.ID = Child.MasterID
Where Child.MonthlyAmount > 1000


Un-normalized:


Select EntityName, Jan07, Feb07, Mar07, ... <- I have to display ALL columns
From YourTable
Where Jan07 > 1000 Or <-- These entries are much too tedious
Feb07 > 1000 Or
Mar07 > 1000 Or <-- Fortunately, there won't be any more than 12 months
Apr07 > 1000 Or in a year.
May07 > 1000 Or
Jun07 > 1000 Or <-- If these were department names, it would be really
Jul07 > 1000 Or tough to add an additional one. All forms, reports,
Aug07 > 1000 Or tables, queries would have to be modified.
Sep07 > 1000 Or
Oct07 > 1000 Or
Nov07 > 1000 Or
Dec07 > 1000


Use the Search Facility here and look for "normalization" and "normalisation",
it will save you a lot of time and effort in the long run.

Wayne