Like Operator to include null values in Search

[Jamesss]

I have generated a search form that calls a query to filter only values in a field that are similar (LIKE) to the field entered into a textbox/combobox.

In the criteria for any given field I have used:

Like [Forms]![FormName].[TextboxName] & "*"

The default value of the Textbox is "". But when I do not enter any search string the query filters the entries with Null entry in the selected field.

How could I include Null or Empty entries. Or in other words if I do not enter any thing in the search box how do I generate an unfiltered Query for that field.

I know I could just create a separate query for when TextboxName = "" but can I do this in a single query?

James

 

[KenHigg]

Stab in the dark:

(Like [Forms]![FormName].[TextboxName] & "*") or IsNull

???

 

[Jamesss]

I tried that previously and it while it does include Null entries in the unfiltered query it also inlcludes them even when I enter a search within another field.

It the same when I enter "Is Null" in the Or Field below the Criteria box it will include Null entries in the default search (without search parameters)....but then when a search string is passed it will still include null values regardless if the entry is LIKE the input Texbox String.

The problem is since I have multiple search boxes applying to different fields.

My search tool has four search Textboxes to which you may enter a search string. When I execute an empty search, a query is opened using the criteria in each of the four fields. I want to include all entries, however Access does not include entries where there is a Null value in one field.

Like [Forms]![FormName].[TextboxName] & "*"

If I use:

Like [Forms]![FormName].[TextboxName] & "*" Or Is Null

Access does inlcude the Null field entries but when I search with a search string entered it will still include ALL Null values within the field wherer we have assigned "Or Is Null".

I still wanted the Null values in Field1 to still appear where I am searching in Field2, Field3 or Field4.

I don't think I can just make a new query for when the search is empty since the filled search will ALWAYS include entries where it is Null in the other field or NEVER include Null entries in an empty search.

ech!

 

[evanscamman]

Try:

iif(Forms!myform!SearchBox = "",True,mytable.TextField is like Forms!myform!Searchbox & "*")

Then set criteria to 'True'

When search box is empty all records are passed, otherwise they are compared with Textbox


Evan

 

[Atomic Shrimp]

It the same when I enter "Is Null" in the Or Field below the Criteria box it will include Null entries in the default search (without search parameters)....but then when a search string is passed it will still include null values regardless if the entry is LIKE the input Texbox String.

Each 'Or' row in the visual query designer is interpreted as a separate set of conditions - so you would need to duplicate any other relevant criteria in that row too.

For example, in the screenshot here, the first criteria row is looking for anything with 'apple' in the name, as long as the price value is greater than 0.85, but the second row only specifies a criterion for the price column, which will make it return every row with a price less than 10, regardless of the name.
(meaning that it will also return rows with 'apple' in the name, at prices less than 0.85, if there are any)

 

[Killdozer]

I only wished I got as far as you. I need to create a query using the like function, with wild card * in front and end of the search word. But I want the word to come from a text box. I tried your string: Like [Forms]![FormName].[TextboxName] & "*". And set the default value in the test box to ""
But I get all files listed not just the one matching the word from the text box.

Any suggestions

Phil

 

[evanscamman]

Trye the "*" in front and behind like this:

Like "*" & [SearchPhraseFormatted1] & "*"

 

[Killdozer]

Trye the "*" in front and behind like this:

Like "*" & [SearchPhraseFormatted1] & "*"

Yes that works, how ever I noticed that I had to close the form and reopen or else I would get the same results regardless of the search word.

Thanks a bunch

Killdozer

 

[evanscamman]

Where are the results for your query showing up? In a SubForm?

If so, in the AfterUpdate event of your textbox put:

MySubForm.Requery

 

[Killdozer]

Where are the results for your query showing up? In a SubForm?

If so, in the AfterUpdate event of your textbox put:

MySubForm.Requery

Yes it shows in a subform, I will try what you said, and let you know how it works. Just to clarify to I type: MySubform, or the actual name of my subform

Thanks

 

[evanscamman]

Yes - the name of your subform naturally - not the name of mine.

 

[Jamesss]

Sorry for not updating you earlier.

Thanks Evan your solution works great!

I also used the wildcard on the left hand side in my search too. It provides for a more robust search tool!