Pull Field name from a form

G

Guest

Howdy,

I'm using the following SQL to drive an Access Chart:

SELECT qry_Test_NormalizedSales1.Week AS Expr1,
Sum(qry_Test_NormalizedSales1.NWeekSales) AS SumOfNWeekSales
FROM qry_Test_NormalizedSales1
GROUP BY qry_Test_NormalizedSales1.Week, qry_Test_NormalizedSales1.sCliNum,
[sCoNum] & [sLoanNum]
HAVING (((qry_Test_NormalizedSales1.Week) Between
[Forms]![frm_ChartMaster]![FromDate] And [Forms]![frm_ChartMaster]![ToDate])
AND
((qry_Test_NormalizedSales1.sCliNum)=[Forms]![frm_ChartMaster]![ClientSelect]) AND (([sCoNum] & [sLoanNum])=[Forms]![frm_ChartMaster]![CoNum]));

What I'm tring to do is replace the Filed [NWeekSales] something like:
Metric: [Forms]![frm_ChartMaster]![Combo19]

Which will allow the user to select which of the available metrics, he/she
would like to graph.

I also tried:
Metric: "["&[Forms]![frm_ChartMaster]![Combo19]&"]"

Which didn't work either. The error I receive is the : "The Expressiis
typed incorrectly or is too complex to be evaluated."

Any help would be greatly appreciated.
 
T

Tom Ellison

Dear B:

You're not going to succeed with this line of thinking. Column names are
not subject to indirection. There are two choices:

- You could put conditional logic in the query that tests the combo box and
selects one of the columns

- You can generate the SQL for the query in code, and place the name of the
column desired into the query code.

Tom Ellison
 
G

Guest

Hmmm

Well actually, the following seems to work:
Expr1:
IIf([Forms]![frm_ChartMaster]![Combo19]="nweeksales",[nweeksales],IIf([Forms]![frm_ChartMaster]![Combo19]="NWeekCredits",[nweekcredits]))

However, it's inelegant at best and not very dynamic. Plus I need to nest
another 4 iif statements. There must be a better way in ther query.

What is the code solution?


Tom Ellison said:
Dear B:

You're not going to succeed with this line of thinking. Column names are
not subject to indirection. There are two choices:

- You could put conditional logic in the query that tests the combo box and
selects one of the columns

- You can generate the SQL for the query in code, and place the name of the
column desired into the query code.

Tom Ellison


Bdavis said:
Howdy,

I'm using the following SQL to drive an Access Chart:

SELECT qry_Test_NormalizedSales1.Week AS Expr1,
Sum(qry_Test_NormalizedSales1.NWeekSales) AS SumOfNWeekSales
FROM qry_Test_NormalizedSales1
GROUP BY qry_Test_NormalizedSales1.Week,
qry_Test_NormalizedSales1.sCliNum,
[sCoNum] & [sLoanNum]
HAVING (((qry_Test_NormalizedSales1.Week) Between
[Forms]![frm_ChartMaster]![FromDate] And
[Forms]![frm_ChartMaster]![ToDate])
AND
((qry_Test_NormalizedSales1.sCliNum)=[Forms]![frm_ChartMaster]![ClientSelect])
AND (([sCoNum] & [sLoanNum])=[Forms]![frm_ChartMaster]![CoNum]));

What I'm tring to do is replace the Filed [NWeekSales] something like:
Metric: [Forms]![frm_ChartMaster]![Combo19]

Which will allow the user to select which of the available metrics, he/she
would like to graph.

I also tried:
Metric: "["&[Forms]![frm_ChartMaster]![Combo19]&"]"

Which didn't work either. The error I receive is the : "The Expressiis
typed incorrectly or is too complex to be evaluated."

Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
Top