Search Query Not working

Question

I have a search form that is set up with 3 text boxes. Each box searches the same query just a different column. The expression I have for criteria is top one, for the name. The only difference is the other 2 boxes point to their text box. This does not work at all but if i change it from " to ' it works but you have to be exact for it to show results. 

Like "*" & [Forms]![frmSearch]![txtname] & "*" 

Can anyone explain this to me or give me an idea of how to better set up the query?

Answer 1

The use of the LIKE operator and the asterisk wildcard character to allow for optional parameters is far from reliable as it can return specious substring matches, and does not allow for Nulls in the column.  If the parameter is left Null any row where the column in question is Null will not be returned by the query.  This is because Null, being the absence of a value, can never equate to anything, not even to Null; the result will be Null, neither True nor False.

A better basis for restricting a query on multiple parameters where the parameters might be used singly or in combination is that in the WHERE clause each parameter is tested in this way:

WHERE (SomeColumn = [some parameter]

  OR [some parameter] IS NULL)

AND (SomeOtherColumn = [some other parameter]

  OR [some other parameter] IS NULL)

AND etc

The following can of course be used:

    (SomeColumn LIKE "*" [some parameter] & "*"

      OR [some parameter] IS NULL)

where it is appropriate to use pattern matching rather than testing for equality.  Bear in mind, however, that even when applied to a single column this can result in specious mismatches.  Moreover, the LIKE operator does not allow use of the indexes, so can reduce performance significantly.  In most cases it is better to reference a combo box in an unbound dialogue form, or in a bound form's header, as the parameter.  The user can then select a value from a fixed set of known values, or by entering the first few characters in the combo box, progressively go to the first match by virtue of the control's AutoExpand property.  The following query is an example:

SELECT [FirstName] & " " & [LastName] AS FullName, Address, City, Region,

Country, Employer, LastName, FirstName, Contacts.ContactID

FROM (Countries INNER JOIN Regions ON Countries.CountryID = Regions.CountryID)

INNER JOIN (Employers INNER JOIN ((Cities INNER JOIN Contacts

ON Cities.CityID = Contacts.CityID) INNER JOIN ContactEmployers

ON Contacts.ContactID = ContactEmployers.ContactID)

ON Employers.EmployerID = ContactEmployers.EmployerID)

ON Regions.RegionID = Cities.RegionID

WHERE (Cities.CityID = Forms!frmReportDialogue!cboCity

    OR Forms!frmReportDialogue!cboCity IS NULL)

AND (Employers.EmployerID = Forms!frmReportDialogue!cboEmployer

    OR Forms!frmReportDialogue!cboEmployer IS NULL);

This example is taken from the section on 'Retrieving data from the database' in my DatabaseBasics demo file, which can be found in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

The logic behind this approach is extremely simple and consequently, given good indexing in the table, very efficient.  It also has the advantage of not having to bother about the data type of the column in question, so unlike when building an SQL statement in code, consideration does not need to be given as to whether the values need delimiting or not.  

Each OR operation is enclosed in parentheses to force it to evaluate independently.  These parenthesized expressions are then tacked together with AND operations.  The way it works is that each parenthesized OR expression will evaluate to TRUE for each row where the value in the column is that of the parameter, or if the parameter is left empty (NULL),  for every row.  By virtue of the AND operations  the WHERE clause as a whole will evaluate to TRUE for those rows where all of the parenthesized expressions evaluate to TRUE, so those rows will be returned.

Note that when you do this, parameters should only be declared if they are of DateTime data type.  If other types were declared they could never be Null.  DateTime parameters are unusual in this respect, and it's always advisable to declare them to avoid their being misinterpreted as arithmetical expressions rather than dates.

When building a query like this, the basic unrestricted query can be built in query design view, but the WHERE clause should always be written and, most importantly, saved in SQL view.  This applies to the initial saving of the query, and if any subsequent amendments are made.  If it's saved in design view Access will move things around and at best the logic will be obscured, at worst it might become too complex to open.  It's a good idea to save the SQL of such queries in a text file in Notepad or similar, as if anything does go wrong you then have something to copy and paste back into the query designer in SQL view.

Note BTW that if searching on the basis of a date range this can be made a closed range or open ended in either direction by treating the start and end date parameters independently, rather than within a BETWEEN….AND operation:

 WHERE (DateColumn >= [some parameter]

  OR [some parameter] IS NULL)

AND (DateColumn < [some other parameter]+1

  OR [some other parameter] IS NULL)

Answer 2

that does not work at all.

Answer 3

Can you show us the whole code you are using? And the SQL for the query?

I don't understand what you mean by "if i change it from " to ' it works".

a search form should be creating a Filter rather than applying criteria to a SQL statement.

Answer 4

Hi,

try with

Like "'*" & [Forms]![frmSearch]![txtname] & "*'" 

Ciao Mimmo