Simple 4 field form query help needed

  • Thread starter Thread starter wazza_c12
  • Start date Start date
W

wazza_c12

Hi Everyone

I have become very frustrated over a seemingly simple query (not that I
am experienced in queries)

I am setting up a simple document indexing database where people can
use forms to enter document information (title, author, location on
server etc) and can use a search form to search for documents on a 4
key fields.

I want the user to be able to enter information in however many of
those 4 fields they wish, then return all documents whose fields either
match the search criteria or are null.

This is what I now have

What am I doing wrong, no doubt it is a simple issue

Thanks for any help

Wazza
--------------------------------
SELECT Details.*
FROM Details
WHERE (((Details.Title) Like "*" & Forms!Search_Form!ffTitle & "*") Or
(Details.Title) Is Null And ((Details.Plan_Category) Like "*" &
Forms!Search_Form!ffCategory & "*") Or (Details.Plan_Category) Is Null
And ((Details.Management_Unit) Like "*" &
Forms!Search_Form!ffManagement_Unit & "*") Or (Details.Management_Unit)
Is Null And ((Details.Keywords) Like "*" & Forms!Search_Form!ffKeywords
& "*") Or (Details.Keywords) Is Null);
----------------------------------

This has 2 issues...
If I search just on title it returns not all records, but at least one
record thats title does not match (ie. I have 5 test records, one
contains the numeral "2" in the title, yet if I search for "2", 4
records are returned.)

Also, if I string toogether 2 criteria (ie title "test" and category
"Cyclone") in my search form then it returns all 5 test entries, 4 of
which contain "test" in the title and only 1 has Cyclone as a category.
The fith record does not contain test in the title an is null for the
category field.
 
I think you have your parands in the wrong places. You need to group the
ORs together.

SELECT Details.*
FROM Details
WHERE
((Details.Title) Like "*" & Forms!Search_Form!ffTitle & "*" Or
(Details.Title) Is Null )

And

((Details.Plan_Category) Like "*" & Forms!Search_Form!ffCategory & "*" Or
(Details.Plan_Category) Is Null)
And

((Details.Management_Unit) Like "*" & Forms!Search_Form!ffManagement_Unit &
"*" Or (Details.Management_Unit)
Is Null )

And

((Details.Keywords) Like "*" & Forms!Search_Form!ffKeywords & "*" Or
(Details.Keywords) Is Null);
 
Back
Top