Form based multi-parameter query & null fields

[Guest]

I have set up a form-based parameter query to search different fileds. The
more fields a user fills in, the more specific the search. My database
searchs a medical journal database. A user can search by: AUTHOR1, AUTHOR2,
AUTHOR3, AUTHOR4, KEYWORD1, KEYWORD2, KEYWORD3, KEYWORD4. However, what if
the user just uses a couple of the above criteria, some of the fields will be
null. Can someone tell me what expression to use in the query to retrieve
records based on the fields indicated by the user? Here is what I have so
far:

SELECT *
FROM ARTICLE
WHERE ((ARTICLE.AUTHOR_1 Like Forms!FIND_ARTICLE!cboAUTHOR1 & '*') And
(ARTICLE.AUTHOR_2 Like Forms!FIND_ARTICLE!cboAUTHOR2 & '*') And
(ARTICLE.AUTHOR_3 Like Forms!FIND_ARTICLE!cboAUTHOR3 & '*') And
(ARTICLE.AUTHOR_4 Like Forms!FIND_ARTICLE!cboAUTHOR4 & '*') And
(ARTICLE.KEYWORD_1 Like Forms!FIND_ARTICLE!cboKEYWORD1 & '*') And
(ARTICLE.KEYWORD_2 Like Forms!FIND_ARTICLE!cboKEYWORD2 & '*') And
(ARTICLE.KEYWORD_3 Like Forms!FIND_ARTICLE!cboKEYWORD3 & '*') And
(ARTICLE.KEYWORD_4 Like Forms!FIND_ARTICLE!cboKEYWORD4 & '*'));

TIA,

Estevan

 

[Allen Browne]

Try this approach:

SELECT *
FROM ARTICLE
WHERE ((Forms!FIND_ARTICLE!cboAUTHOR1 Is Null OR
ARTICLE.AUTHOR_1 Like Forms!FIND_ARTICLE!cboAUTHOR1 & '*')
And (Forms!FIND_ARTICLE!cboAUTHOR2 Is Null OR
ARTICLE.AUTHOR_2 Like Forms!FIND_ARTICLE!cboAUTHOR2 & '*')
And ...

BTW, you do realise that if an article has "Watson" in Author1 and "Crick"
in Author2, and you chose Crick in cboAUTHOR1 that it would not find the
article? Same problem with the keywords: you only get the match if you put
the keyword in the correct box.

The solution to that issue would be to create related table of article
keywords, with fields:
ArticleID foreign key to the primary key of the Article table.
Keyword the keyword for that article.
You can now enter as many keywords as you want for the article (one per
row), and you only have a single field to search to get a match.

Same with the authors.

 

[Guest]

Thanks Allen. It works great! I appreciate your help.