Queries from Combo Boxes...HELP!!

  • Thread starter Thread starter BrianH
  • Start date Start date
B

BrianH

Hi,

I have created a database with multiple tables that include monthly
budget data. In order to compare these monthly figures I have created
a form in which I can select (from combo boxes) which fields (the
months) I want from each table.

However, I cannot get the query to recognize that I want to select the
fields from the form. If I could just plop criteria from one of the
tables fields that would be fine, but I can't. I am trying to select
the fields that I pulled down from the combo boxes. For example, right
now there are three combo boxes, I can pull down January from each box
which is from the correct tables. I press my command button and go to
the query. This is where my problem arises. I cannot seem to figure
out how to manipulate the query in order to search for the fields and
tables that I selected from the form.

PLEASE HELP

thanks soooo much in advance!

~Brian
 
This would be much easier if each month were a record rather than a field,
which is what you would have if your table were normalized properly. You can
fake normalization with a UNION query

(SELECT JanuaryBudget AS MonthBudget, "January" AS MonthName FROM Budget)
UNION
(SELECT FebruaryBudget AS MonthBudget, "February" AS MonthName FROM Budget)
UNION
....etc.

Once you save that as a query, you can then easily use MonthName in the
WHERE clause of your query based on the value of the pull down.

HTH;

Amy
 
If you want to be able to choose which fields you want to include, you are
going to have to write the SQL string in VBA using the selections from the
combo boxes to build the string.

What I typically do is create a query that would be used if not selection
criteria is made and save it to use as a template - something like:
qselMonthlyTemplate. I will also save a copy as qselMonthly which is what
the report will use.

At run time, read the query's SQL property into a string variable and modify
it based on the selections. Once the SQL is complete, save it to a name the
report will use as it's data source.

Some brief example air code:

strSQL = CurrentDb.Querydefs("qselMontlyTemplete").SQL
.....
Modify strSQL here
.....
CurrentDb.Querdefs("qselMonthly").SQL = strSQL
 
Here is my SQL for this query as the basic or template query.

SELECT [215 Actuals YTD May].ACCOUNT_CODE, [215 Actuals YTD
May].ACCOUNT_TITLE, [215 Actuals YTD May].Jan, [215 OP].Jan, [215
SI].Jan
FROM ([215 Actuals YTD May] INNER JOIN [215 OP] ON [215 Actuals YTD
May].ACCOUNT_CODE = [215 OP].F1) INNER JOIN [215 SI] ON [215 OP].F1 =
[215 SI].Field1;


What can I do to alter it and make it conform with my form?

thanks!!

~Brian
 
Back
Top