Problem with IIf statement in the OR clause

[Andy_Khosravi]

I have been trying to build a user friendly search engine for a small
database I have created. I'm having some particular problems with one
of my date fields.

Here's the setup:

I'm using Access 97 (I know it's old. But, it's the tool they
give me to work with)
My working knowledge of SQL is on the low side.
My working knowledge of VBA is beginner.

I have three tables that concern this query. The main table is called
'tblBenIssues' and is the central table. Each instance of
'BenIssues' can have many comments (stored in tblComments') and
many locations (stored in 'tblDPTOwnerHist').

I need this query to read inputs from 38 different search criteria
fields on a search screen (those fields reference the three tables
listed above), and then display the results of that query on a results
summary form.

My main problem has always been dealing with null fields. To get around
the problem of null values not being returned when criteria fields were
blank, I created a small function which inserts a "*" into a
criteria string. I then set all my text fields to accept zero length
strings, and defaulted them to "". The query I build then calls the
function from the criteria line, and even blank fields are returned
with the "*" criteria.

I have three queries. The first query searches the comments table for
matches, then sends the matches on to another query which checks those
matches for location history matches. The results of this 2nd query are
then sent to a 3rd and final query, which matches the criteria vs. the
main table.

This works perfectly (so far as I can tell) for all of my fields except
date fields. For those I've always just had the functions dummy in
dates that will capture every single record (I.E. 01/01/1801 -
01/01/2099). This takes care of 3 out of the 5 date fields I have.
However, 'Date Closed' (date issue was closed) and 'End Date'
(Time stamp of when an issue left a department) can sometimes be null.

Now we get to my problem. I thought I could easily take care of this by
putting the following criteria in the Access Query builder.
CRITERIA: Between [forms]![frmsearch]![DateEnd1] And
[forms]![frmsearch]![DateEnd2]
OR: IIf([forms]![frmsearch]![dateend1] = #01/01/1801#,Is
Null,#1/1/1801#)

The idea here is that it will search the primary criteria first. If
those criteria fields are dummy dates, 'Is Null' is inserted in the
OR statement so all records will be returned. If the dates are not
dummy dates, then 01/01/1801 (a date for which no matches will ever be
found) is inserted in the OR statement.

The problem? Access does not seem to be evaluating the OR statement.
Even when I set both the True and False parts of the IIF statement to
'Is NULL', the query results behave as if the OR statement wasn't
even there. I tried changing the forms! reference in dozens of
different ways thinking that perhaps it was not evaluating the
information I thought it was, but no dice. However, if I remove the Iif
statement and just say 'Is Null', it evaluates the 'Is Null'
just fine.

Here is a copy paste of the SQL statement from the 2nd query (where the
problem is)

SELECT tblDptOwnerHist.HistID, tblDptOwnerHist.IssueID,
tblDptOwnerHist.OwnerID, tblDptOwnerHist.Department,
tblDptOwnerHist.DateBegin, tblDptOwnerHist.DateEnd,
tblDptOwnerHist.Reason, tblDptOwnerHist.SubReason,
tblDptOwnerHist.SubDepartment
FROM qryBadMojo INNER JOIN tblDptOwnerHist ON qryBadMojo.IssueID =
tblDptOwnerHist.IssueID
WHERE (((tblDptOwnerHist.OwnerID) Like srcOwnerID())
AND ((tblDptOwnerHist.Department) Like srcDepartment())
AND ((tblDptOwnerHist.DateEnd) Between [forms]![frmsearch]![dateend1]
And [forms]![frmsearch]![dateend2]) AND ((tblDptOwnerHist.Reason) Like
srcReason())) OR
(((tblDptOwnerHist.DateEnd)=IIf(IsNull([forms]![frmsearch]![dateend1]),([tblDptOwnerHist].[DateEnd])
Is Null,#1/1/1801#)));

All other parts of the query are working as intended. The only part of
it that isn't working is the part after the OR statement.

Any comments or suggestions will be greatly appreciated!

On an unrelated side note, I have a sinking feeling that I'm going
about this whole process in the most fantastically inefficient way
possible. Comments about efficiency are also welcome =)

[Bob Quintal]

An***********@bcbsmn.com wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

I have been trying to build a user friendly search engine for
a small database I have created. I'm having some particular
problems with one of my date fields.
[[SNIP]]
OR
(((tblDptOwnerHist.DateEnd)=IIf(IsNull([forms]![frmsearch]! [dat eend1]),([tblDptOwnerHist].[DateEnd]) Is Null,#1/1/1801#)));

All other parts of the query are working as intended. The only
part of it that isn't working is the part after the OR
statement.
At first glance, you have the statement
([tblDptOwnerHist].[DateEnd]) IS null nested in your IIF
statement.That won't work.

To handle the null, add a field to the grid:
exprN: Isnull([tblDptOwnerHist].[DateEnd]) AND
IsNull([forms]![frmsearch]![dateend1])

Make an entry on a separate criteria row of the word 'true'
without the quotes, which matches when both contain a date and
both are null. You use another criteria row to match the dates.

Any comments or suggestions will be greatly appreciated!

On an unrelated side note, I have a sinking feeling that I'm
going about this whole process in the most fantastically
inefficient way possible. Comments about efficiency are also
welcome =)

It is usually more efficient to build SQL for searching in code,
and only add the filters required.

--
Bob Quintal

PA is y I've altered my email address.

[Andy_Khosravi]

Thank you for the response Bob, I will give your suggestion a try.

I considered building an SQL statement by concatenating the parts of
the query together in a string. However, as far as I know a string is
limited to 256 characters. The SQL statements that would result from
this query would typically exceed 256 characters, and I couldn't
think of a way around that limitation.

I'm sure there has got to be a solution for it..

[Bob Quintal]

An***********@bcbsmn.com wrote in
news:11*********************@z14g2000cwz.googlegro ups.com:

Thank you for the response Bob, I will give your suggestion a
try.

I considered building an SQL statement by concatenating the
parts of the query together in a string. However, as far as I
know a string is limited to 256 characters. The SQL statements
that would result from this query would typically exceed 256
characters, and I couldn't think of a way around that
limitation.

I'm sure there has got to be a solution for it..

Number of characters in a Text field= 255.

There are two kinds of strings: variable-length and fixed-length
strings.

A variable-length string can contain up to approximately 2
billion (2^31) characters.
A fixed-length string can contain 1 to approximately 64K (2^16)
characters.

--
Bob Quintal

PA is y I've altered my email address.