SQL WILDCARD FOR ZERO TEXT AND NULL

Question

Windows XP - Office 2007 - Access

I am creating a query to populate a text box.  The operator has a number of textboxes into which they can enter a search parameter relating to supplier details.  For example, the first box enables them to specify all or part of the company name, the second, all or part of the town, the third all or part of one of the keywords stored against the company to indicate the company activity.

As they 'key up' when they are typing the selection in the textbox changes to suit the combination of parameters that have been entered into the various textboxes. Within the SQL the parameter will be shown as, for example, [table_name].[field_name] LIKE ('*smi*') when searching for Smith & Co.  When there is no particular requirement in the textbox the SQL appears as [table_name].[field_name] LIKE ('**').  This is fine so long as there is an entry within this field.  However, if this field happens to be null then the entry is rejected as * requires a text of some sort to be present.  This is not always the case and relevant entries are not appearing due to perfectly legitimate absence of information.

Is there a way of expressing this with a different combination of wildcards or is there a way of setting the default value of a field to 'display' a zero length string that the * wildcard will recognise?

Obviously I can make a very complex procedure that examines all the various parameters and rewrites swathes of SQL each time, but I am hoping for a more elegant solution.

Any help is much appreciated!

Geoff

Answer 1

If the Like actually had something in it to match, that string would still select everything that was null as well.

That's not so.  If the above is entered in the 'criteria' row of a query in design view, which is what Wayne was suggesting, the equivalent SQL would be along these lines:

SELECT DISTINCT CompanyName, <more columns>

FROM Companies INNER JOIN CompanyKeyWords

ON Companies.CompanyID = CompanyKeyWords.CompanyID

WHERE (CompanyName LIKE "*" & Forms!FormName!txtCompany & "*"

OR Forms!FormName!txtCompany IS NULL)

AND (Town LIKE "*" & Forms!FormName!txtTown & "*"

OR Forms!FormName!txtTown IS NULL)

AND (KeyWord LIKE "*" & Forms!FormName!txtKeyWord & "*"

OR Forms!FormName!txtKeyWord IS NULL)

< and so on>;

Each parenthesised OR operation evaluates independently of the AND operations.  Taking Town as an example, if there is a value at the Town column position in a row and a value has been entered into the txtTown control the parenthesised expression will evaluate to TRUE if the value matches the pattern as the first part of the OR operation will evaluate to TRUE and restrict the rows returned to the matching rows.  If on the other hand there is no value entered into the control, i.e. it is NULL then the second part of the OR operation will evaluate to TRUE for all rows regardless of the value in the Town column, or if there column is NULL, so all rows not restricted by the other criteria will be returned..

This is the normal way of 'optionalising' parameters in a query and should be adopted if you are using a saved query.  If on the other hand you are building the SQL statement in code do as Doug advises and simply exclude the criterion from the WHERE clause.

If you do write and save a query in this way, however, do so in SQL view.  Do not do so in design view or switch from SQL view to design view before saving the query as Access will move things around and the underlying logic, while fundamentally the same, will be obscured and difficult to amend.

Answer 2

Thanks Wayne but that won't work.  If the Like actually had something in it to match, that string would still select everything that was null as well.  Thats OK when, like your example, there is no required match on the field but as soon as we are trying to filter on this field, we obviously want to exclude null values.

Geoff

I'm not sure it meets your requirements but this is a way to change a Null to a ZLS for comparison.

WHERE Nz( [table_name].[field_name], '') LIKE ('**')

Answer 3

OK

Not really sure what you're trying to do then.

If you need "something - anything will do" in the field why not add some vba onLoad ?

if is null([control]) then

control = " "

end if

Of course remove the " " OnClose

Just an idea !

Answer 4

Thanks Wayne but that won't work.  If the Like actually had something in it to match, that string would still select everything that was null as well.  Thats OK when, like your example, there is no required match on the field but as soon as we are trying to filter on this field, we obviously want to exclude null values.

Geoff

Answer 5

Use this on the base query that the form is based on

Forms![FormName]![ControlName] Like “**” Or Forms![FormName]![ControlName] Is Null