Pull Field name from a form

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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
 
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...
 
Back
Top