Text parameter in Crosstab query

[Iceman95]

Hi everyone,

OK, here is my trouble (I use Access 97) :

I designed a crosstab query based on a select query that requires parameters to be entered via a form.

All parameters have been declared in the crosstab query.

Parameters were given different types of value : numeric, text, date or yes/no.

Everything works fine, provided that the parameters are given a non-Null value.

But I wanted the parameters to be optional, i.e when some of the associated controls (mainly drop-down lists) are left blank (contain a Null value), the query must return all records related to those specific parameters.

So I set the following statement for each parameter to be entered : [Forms]![MyForm]![MyParameter] OR LIKE [Forms]![MyForm]![MyParameter] IS NULL.

I've found out after many tests that this works only when the parameters are declared with a Numeric value in the Crosstab query.

When one or more parameters are set to a Text value, the query returns no results when all of the parameters are left blank (whereas it should return ALL records).

Furthermore, the problem seems to be specific to Xtab queries, since the select query works perfectly with the form.

I've tried to use the following statement : [Forms]![MyForm]![MyParameter] OR LIKE [Forms]![MyForm]![MyParameter]="", but it gives no better result.

Is this problem specific to Access 97 ?

I've included a DB example (coutesy of Jon K from another post - see the "Query1-a" query).

Thanks in advance for any clue.

Tom

 

[Pat Hartman]

You are using Like inappropriately. Like is ONLY used when you have a partial value that you want to use with wildcards to perform a pattern serach. Change the criteria to:

[Forms]![MyForm]![MyParameter] OR [Forms]![MyForm]![MyParameter] IS NULL