Complex Criteria Parameter Query

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I am attempting to develop a complex criteria parameter query (based upon a
form) where Department range is critical. The first, third and fourth lines
execute properly. When I change the data in the form to match line two, no
records are selected. All other conditions will produce the correct results.
What have I coded in error?


Field: Department

First Criteria Line: >=[Forms]![Report Selection].[DeptStart] And
<=[Forms]![Report Selection].[DeptEnd]
Second Criteria Line: <=[Forms]![Report Selection].[DeptEnd]
Third Criteria Line: >=[Forms]![Report Selection].[DeptStart]


Field: DStart: [Forms]![Report Selection].[DeptStart]

First Criteria Line: Is Not Null
Second Criteria Line: Is Null
Third Criteria Line: Is Not Null
Fourth Criteria Line: Is Null

Field: DEnd: [Forms]![Report Selection].[DeptEnd]

First Criteria Line: Is Not Null
Second Criteria Line: Is Not Null
Third Criteria Line: Is Null
Fourth Criteria Line: Is Null
 
Well, according to the way you have it written, the criteria for line 2 reads:

[Department] <= Forms![Report Selection].[DeptEnd] AND
[Department] >= Forms![Report Selection].[DeptStart] AND
Forms![Report Selection].[DeptStart] IS NULL

and you cannot fulfill line two and three of this at the same time since (>=
NULL) evaluates to NULL.

What is it that you are trying to accomplish (describe it in words)? What
do you want the query to return if the DeptStart control is filled in but the
DeptEnd is not? What do you want the query to return if DeptStart is not
filled in, but DeptEnd is?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Dale,

The desired result is if user does not enter either the start department or
the end department it will return all departments. If the start date is null
and the end date is not null it will list all departments less than or equal
to the end department. If only the start department is not null it will list
all departments greater than or equal to the start department. If both fields
are not null it will return the range of the two values.

I have discovered the problem in the current query. There was another field
with a selection criteria in line one and two. Once I removed the criteria in
line two of the unrelated field, the query returned results as desired.

Bruce
 
Back
Top