Solved Help with nulls in query (1 Viewer)

[zebrafoot]

Hello,

I would like a simple search form to return a set of results based on what I enter in a text box on another form. The query underlying the results form has criteria such as:

Like "*" & [Forms]![frmSearch].[Form]![txtSearchString] & "*"

so that if I enter "fred" in the box txtSearchString on the form frmSearch, I get all the results with "fred" in them. That works great. However, if I do this with criteria over multiple fields within the query, the LIKE criterion doesn't work where there are nulls in the database - if I left txtSearchString blank, for example, only results where there was a value in that control would be returned.

To give a bit more context, if it helps clarify, I'm looking to search university departments, using an official title (as per the University's own naming scheme), but also include a nickname (such as "where Betty Bloggs used to work"). Not all the departments will have nicknames. I could modify the database so that all departments have a value for nickname, but I'd rather understand how to do it the way I originally intended.

Any (simple) explanation of how I can do this would be greatly appreciated.

Many thanks,
Pete

 

[theDBguy]

Hi Pete,

Try using a criteria like this:

Like "*" & [Forms]![frmSearch].[Form]![txtSearchString] & "*" OR [Forms]![frmSearch].[Form]![txtSearchString] Is Null

 

[zebrafoot]

Wow, that's quick!

Yes, that works. Thank you, you have saved me hours of mucking about!

Pete

 

[The_Doc_Man]

To start, using LIKE "*" & something & "*" will return just about anything even if your txtSearchString is null OR a zero-length string (ZLS, =""). This is because when you perform the concatenation you have "*" & NULL & "*" which then resolves to "**" - and since that is wild-card followed by wild-card AND for Access, "*" matches zero or more characters of any kind, the only thing you WON'T return would be if the target field (the field you are searching, the thing that appears in front of the LIKE) contains a NULL. Anything else including a ZLS will match that.

To not return the empty fields when your search patter is blank, you need to qualify the search with a WHERE clause that says "WHERE LEN( ''&field) > 0" - that is concatenate a ZLS with the field you are searching, and if the field is NULL or is itself a ZLS, the length of that is zero.

 

[zebrafoot]

Thanks Doc Man,

To clarify - I do want to return all records if theres nothing in the search string. An example:

Search for city = "paris"
nickname = "old department"

In the above case, I'd want to see all the departments in paris, where the nickname = "old department". However, if I put nothing in the nickname field, I'd still want to return any departments that were in the city of paris. The problem was that as nickname is not a required field and the majority of my departments don't have a nickname, I wasn't returning a whole list of hits, only those with a nickname.

Pete

 

[theDBguy]

Wow, that's quick!

Yes, that works. Thank you, you have saved me hours of mucking about!

Pete

Glad to hear you got it to work. Good luck with your project.

 

[The_Doc_Man]

Thanks Doc Man,

To clarify - I do want to return all records if theres nothing in the search string. An example:

Search for city = "paris"
nickname = "old department"

In the above case, I'd want to see all the departments in paris, where the nickname = "old department". However, if I put nothing in the nickname field, I'd still want to return any departments that were in the city of paris. The problem was that as nickname is not a required field and the majority of my departments don't have a nickname, I wasn't returning a whole list of hits, only those with a nickname.

Pete

OK, it was a matter of interpretation. TheDBGuy's answer is closer than the way I read it.