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 =)
