Null Parameter

Show something

If a user doesn't specify a parameter value, you can use a wildcard with the parameter in the format

Like [Enter Name] & "*"

The problem with this is that the query will return records that partially match the criteria.

For instance, if users searching for records based on last name enter a parameter value of "Smith" they'll also get the records for Smithers, Smithfield and Smithson.

Another problem is that the parameter query will ignore any records where the field being searched contains a Null value when you try to return the entire recordset with a blank parameter.

To fix this, set up a query to limit responses to explicit parameter entries, but still allow users to return all records by leaving the parameter blank.

If you're searching for LastName, open the query design grid and add LastName to it.

In the Criteria row for the field, enter the parameter prompt

[Enter Name]

Then, in the next blank column of the design grid, enter the same parameter (everything between and including the square brackets) in the Field text box.

Finally, in the Or row, enter the criteria Is Null .

If you're using any additional criteria for other fields, make sure to copy that criteria to the Or line as well.



See all Topics