Parameter query:Paramater blank

[Guest]

Hi,

I have a report in Access that has a source of a parameter query. The
parameter values are 'A', 'B', 'C' and 'D'. If a user passes any of these
values the report displays results for just that value. If the user doesn't
enter anything in the parameter window, a blank report comes up.

Is there a way to rewrite this query so that if a user leaves the parameter
window blank, a comprehensive report comes up that have the data for all 'A',
'B', 'C' and 'D's? Thanks.

 

[Guest]

Use an IIF statement --
Like IIF([YourControl] Is Null, "*", [YourControl])

 

[Guest]

The usual way to optionalize a parameter is to test for it being NULL:

SELECT *
FROM YourTable
WHERE YourField = [Your Parameter]
OR [Your Parameter] IS NULL;

If the parameter IS NULL the WHERE clause will evaluate to TRUE and
consequently all rows will be returned. The problem with the use of the LIKE
operator and a wildcard character is that if there are any rows in the table
where the YourField column is NULL that row would not be returned, as NULL
Like "*" evaluates to NULL rather than TRUE. If the column's Required
property is True (equivalent to the DDL NOT NULL constraint) then this
situation would not arise of course, but as a generic solution this
constraint cannot be assumed, whereas testing for OR [Your Parameter] IS NULL
is applicable in all circumstances.

Ken Sheridan
Stafford, England