Parameter question for crosstab queries

[Dan]

Hi,

I have a form based on a crosstab query that I use to drill down into
specific data. One criteria of the query is based on a combo box in the form
and it works perfectly when I select the exact number I want as the criteria.

The parameter is also set correctly with the name of the combobox and type =
text.

The criteria in the crosstab query is 'Like [Forms]![FormA]![Combo]' and it
works fine when I select specific criteria such as 0, 0.5, 1, 1.5, or 2.
However I want to also see everything less than 2 and everything, so when I
have the value in my combobox as either <2, <"2", * which normally works for
a query I always get the #Name? error in the form.

What should my value be if I want to view everything less than 2 and
everything including 2?

I've tried changing the type in the parameter to value, integar, decimal,
and nothing works.

thanks
Dan

 

[Ken Sheridan]

Dan:

You can't use a single parameter in this way; you'll need to have to
two separate combo boxes to define a range, with each being optional
so as to allow for open ended 'more than' or 'less than' ranges. This
is done by testing for the parameter being Null, so the WHERE clause
of the query would be like this:

WHERE (YourField >= [Forms]![FormA]![ComboLower]
OR [Forms]![FormA]![ComboLower] IS NULL)
AND (YourField <= [Forms]![FormA]![ComboUpper]
OR [Forms]![FormA]![ComboUpper] IS NULL)

To select a single value you'd select the same value in both combo
boxes, e.g. 2; to select a range you'd select the lower and upper
limits, e.g. 0.5 and 1.5; to select 1 and all values above for
instance you'd select 1 in ComboLower and leave ComboUpper Null; to
return 2 and all values below you'd leave ComboLower Null and select 2
in ComboUpper.

Another method is to have one combo box from which an operator can be
selected by virtue of having a RowSource of:

=;<>;>=;<=

and another combo box to select the value.

The WHERE clause for the query then goes like this:

WHERE (((YourField=[Forms]![FormA]![Combo]
And [Forms]![FormA]![cboOperators]="=")
Or [Forms]![FormA]![cboOperators]<>"=")
Or [Forms]![FormA]![Combo] Is Null)
And (((YourField<>[Forms]![FormA]![Combo]
And [Forms]![FormA]![cboOperators]="<>")
Or [Forms]![FormA]![cboOperators]<>"<>")
Or [Forms]![FormA]![Combo] Is Null)
And (((YourField>=[Forms]![FormA]![Combo]
And [Forms]![FormA]![cboOperators]=">=")
Or [Forms]![FormA]![cboOperators]<>">=")
Or [Forms]![FormA]![Combo] Is Null)
And (((YourField<=[Forms]![FormA]![Combo]
And [Forms]![FormA]![cboOperators]="<=")
Or [Forms]![FormA]![cboOperators]<>"<=")
Or [Forms]![FormA]![Combo] Is Null)

Ken Sheridan
Stafford, England