Help - null values and wildcards (1 Viewer)

[chrisctk]

Hi,

Firstly, please forgive me if i am not using the correct 'language' - i am slowly trying to teach meself how to use Access!

I am having real trouble with creating a query where the criteria runs off a form. Basically, I want a 'keyword' search box on a form where the user can type in a single word, and if that word appears anywhere within the 'comments' column, the query should show that row. If the 'keyword' search box is left empty - the query should return everything.

I have almost got this working using the criteria below - but here is my problem...

Like "*" & [Forms]![frmMI]![Comments] & "*"

If i type a keyword in on the form, the query works great. BUT, if i leave the keyword cell on the form blank, the query only returns rows are not null in the comments column.


To help explain..

In my Comments table, 200 rows do not contain any comments, and 100 do. With the code I am using at the moment, if I leave the 'keyword' search box on the form empty, the query only returns the 100 rows with anything in the comments box.

Please please help - its been driving me mad for about 2 days now!

Many thanks....Chris

 

[namliam]

Like "*" & [Forms]![frmMI]![Comments] & "*"

Empty (or NULL as the technical term is) is NEVER equal to anything, even Null = Null is not right.
Null Is Null is the 'proper' syntax.

If you want a query to return all if you have a null / empty search, you either have to use a 'proper' "search form" (search on the forum and find the samples)
Or use something in the query to identify that the search text is blank, the search form however is much more flexible, so my reconmendation would be to get used to it now before addopting a half way solution.

 

[FoFa]

options: NZ([yourcolumn],"") Like "*" & [Forms]![frmMI]![Comments] & "*"

[youorcolumn] IS NOT NULL AND [yourcolumn] Like "*" & [Forms]![frmMI]![Comments] & "*"

 

[namliam]

options: NZ([yourcolumn],"") Like "*" & [Forms]![frmMI]![Comments] & "*"

This is an intresting one though its not exactly the 'best' solution IMHO, it does do the trick of finding all when nothing is entered in [comments]

[youorcolumn] IS NOT NULL AND [yourcolumn] Like "*" & [Forms]![frmMI]![Comments] & "*"

This doesnt do anything that
[yourcolumn] Like "*" & [Forms]![frmMI]![Comments] & "*"
doesnt do...