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
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