Parameter Queries

[Ronald Dodge]

Access 2002

Is there a way to not only have a prompt for values to a criteria, but also
if the user don't put in a value for the parameter, then it doesn't use that
particular criteria?

From what I understand, when a user leaves a parameter blank, it returns as
a "NULL" value, which then causes no records to be returned. I like to see
if we can get around this issue without having to create multiple queries,
as I see this unuserfriendly if there is no way to get around it other than
having to create either multiple queries or having to custom code and use
recordsets instead.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[John Spencer]

If the field is text and always has a value then you can use
Field: SomeField
Criteria: LIKE Nz([Find What],"*")

Or if you don't care if you use the index or not and the field may have
nulls
Field: SomeField: SomeTable.SomeField & ""
Criteria: LIKE Nz([Find What],"*")

OR if the field may have nulls and you want to use the index
Field: SomeField
Criteria: [Find What] OR [Find What] is Null
(Be aware that Access will reformat the query when you save it and if you
use several of these the query can become "too complex". The reformatting
consists of placing the parameter into a field "cell" and checking if the
parameter value is null, not checking if the field matches the parameter,
and checking any other criteria; and building a second line of criteria
where it checks to see if the field matches the parameter and the other
criteria.)

For number and date fields that are never null you can either specify ranges
or use the following, which compares the value of the field against itself.
Again nulls will be screened out since nulls are NEVER equal to anything -
including other null. Null = Null returns Null
Field: SomeField
Criteria: Nz([Find What],[SomeField])

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jamie Collins]

Is there a way to not only have a prompt for values to a criteria, but also
if the user don't put in a value for the parameter, then it doesn't use that
particular criteria?

From what I understand, when a user leaves a parameter blank, it returns as
a "NULL" value, which then causes no records to be returned. I like to see
if we can get around this issue without having to create multiple queries

Consider the following logic for an optional parameter value using
Northwind and ANSI-92 Query Mode SQL DDL (http://tinyurl.com/3cbwfl):

CREATE PROCEDURE GetEmployeeDetails
(
arg_EmployeeID INTEGER = NULL
)
AS
SELECT EmployeeID, LastName, FirstName, Title
FROM Employees
WHERE EmployeeID = IIF(arg_EmployeeID IS NULL, EmployeeID,
arg_EmployeeID);

However, some people prefer the following (based on expected
performance rather than logic, I suspect):

WHERE (EmployeeID = arg_EmployeeID
OR arg_EmployeeID IS NULL);

Jamie.

--

 

[Ronald Dodge]

Thank you for your SQL version of this. As I tested this out, you can also
do this via the QBE method.

First, put in the same prompt as a separate field within the query.

Then as an "Or" comparison operator, type in "Is Null" in a new row under
that particular field.

About the only thing that I don't see in the QBE method that could easily be
done via SQL statements is the grouping of such criterias. I will however
address that as I cross that road, which I'm about to cross it.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000