Jet not recognizing expression 1

[swaybright]

Hi all,
I am gettin the following error:
"The Microsoft Jet database engine does not recognize '[forms]![chartoptions]![combo7]' as a valid field name or expression"

I have a chart that is based on a query. I want to give the user the option to limit the data set of the query based on values selected from combo boxes. So on the form with the chart I have 4 combo boxes with values. The sql statement is below:

SELECT LevelData.Level1, LevelData.Haze, ResinList.ResinName, ResinList.ResinType, ResinData.RDResearchID
FROM (ResinList INNER JOIN ResinData ON ResinList.ResinID = ResinData.Resin) INNER JOIN LevelData ON ResinData.ResinDataID = LevelData.ResinDataID
WHERE (((LevelData.Level1) Is Not Null) AND ((ResinList.ResinType)=&quot;cRCP&quot AND ((ResinData.RDResearchID)=67) AND ((IIf(IsNull([forms]![chartoptions]![combo7]),&quot;&quot;,[resinlist].[resintype]=[forms]![chartoptions]![combo7]))<>False) AND ((IIf(IsNull([forms]![chartoptions]![combo9]),&quot;&quot;,[resinlist].[synthesis]=[forms]![chartoptions]![combo9]))<>False) AND ((IIf(IsNull([forms]![chartoptions]![combo11]),&quot;&quot;,[resinlist].[resinname]=[forms]![chartoptions]![combo11]))<>False) AND ((IIf(IsNull([forms]![chartoptions]![combo13]),&quot;&quot;,[resinlist].[mfr]=[forms]![chartoptions]![combo13]))<>False)) OR (((LevelData.Level1) Is Not Null) AND ((ResinList.ResinType)=&quot;cHP&quot AND ((ResinData.RDResearchID)=67) AND ((IIf(IsNull([forms]![chartoptions]![combo7]),&quot;&quot;,[resinlist].[resintype]=[forms]![chartoptions]![combo7]))<>False) AND ((IIf(IsNull([forms]![chartoptions]![combo9]),&quot;&quot;,[resinlist].[synthesis]=[forms]![chartoptions]![combo9]))<>False) AND ((IIf(IsNull([forms]![chartoptions]![combo11]),&quot;&quot;,[resinlist].[resinname]=[forms]![chartoptions]![combo11]))<>False) AND ((IIf(IsNull([forms]![chartoptions]![combo13]),&quot;&quot;,[resinlist].[mfr]=[forms]![chartoptions]![combo13]))<>False));

Note the query alone works properly. I have tested it without the chart. I get the error when the chart is present.

Shane

 

[GingerR]

in the query design, right-click in the grey part above the query grid and choose PARAMETERS.

put in [forms]![chartoptions]![combo7] and the data type.

you'll probably have to do the same with all parameteres you're referencing.

suggestion on diff topic: it would be better to name your combo boxes something more descriptive, like cboName, instead of combo23. better for you, and for whoever inherits your db ) just an idea....

g

 

[swaybright]

OK,
I realized that a crosstab query requires explicit parameters. So this takes care of the error problem.

Now I have an expression problem. I want the query to return all values of the query if the user leaves the combo box null and filter values according to the combo box value chosen.

Right now if the user leaves the box null, no values are returned.

How do I correct this?

shane

 

[swaybright]

Thanks for the help Ginger. I was writing as you were. You are right about the combo boxes.

Any ideas about the other issue?

Shane

 

[GingerR]

try this instead for your criteria:

Like &quot;*&quot; & [Forms]![Form4]![combo0]

 

[swaybright]

Good call.
I have taken it one step farther and included &quot;(ALL)&quot; as a choice in the combo box as per the FAQ in this forum and set my Where clause to include
((IIf(([forms]![resinchart]![CBoType]=&quot;(All)&quot,&quot;Like *&quot;,[resinlist].[resintype]=[forms]![resinchart]![CBoType]))<>False)

Thanks for the advice.

Shane

PS I renamed my combo boxes