Returning records that partially match a value

Question

I'm trying to get a query working that takes the values (sometimes just the first part of a string) from a form control. The problem I have is that it only returns records when the full string is typed in.

i.e. in the surname box, I should be able to type gr, and it brings up

green grey graham

but at present it's not bringing up anything uless the full search string is used.

There are 4 search controls on the form in question, and they are only used in the query if the box is filled in.

The query is :

SELECT TabCustomers.*,
       TabCustomers.CustomerForname AS NameSearch,
       TabCustomers.CustomerSurname AS SurnameSearch,
       TabCustomers.CustomerDOB AS DOBSearch,
       TabCustomers.CustomerID AS MemberSearch
FROM TabCustomers
WHERE IIf([Forms]![FrmSearchCustomer]![SearchMember] Is Null
          ,True
          ,[Forms]![FrmSearchCustomer]![SearchMember]=[customerid])=True
      AND IIf([Forms]![FrmSearchCustomer].[SearchFore] Is Null
              ,True
              ,[Forms]![FrmSearchCustomer]![SearchFore] Like [customerforname] & "*")=True
      AND IIf([Forms]![FrmSearchCustomer]![SearchLast] Is Null
              ,True
              ,[Forms]![FrmSearchCustomer]![SearchLast] Like [customersurname] & "*")=True
      AND IIf([Forms]![FrmSearchCustomer]![Searchdate] Is Null
              ,True
              ,[Forms]![FrmSearchCustomer]![Searchdate] Like [customerDOB] & "*")=True;

Answer 1

There is an Access Method for that!

If you have your "filter" controls on the form, why don't you use the Application.buildCriteria method, that will allow you to add your filtering criterias to a string, then make a filter out of this string, and build your WHERE clause on the fly?

selectClause = "SELECT TabCustomers.* FROM TabCustomers"
if not isnull(Forms!FrmSearchCustomer!SearchMember) then
    whereClause = whereClause & application.buildCriteria(your field name, your field type, your control value) &  " AND "
endif
if not isnull(Forms!FrmSearchCustomer!SearchFore) then
    whereClause = whereClause & application.buildCriteria(...) &  " AND "
endif
if not isnull(Forms!FrmSearchCustomer!SearchLast) then
    whereClause = whereClause & application.buildCriteria(...) &  " AND "
endif
if not isnull(Forms!FrmSearchCustomer!SearchDate) then
    whereClause = whereClause & application.buildCriteria(...) & " AND "
endif
--get rid of the last "AND"
if len(whereClause) > 0 then
     whereClause = left(whereClause,len(whereClause)-5)
     selectClause = selectClause & " WHERE " & whereClause
endif
-- your SELECT instruction is ready ...

EDIT: the buildCriteria will return (for example):

• 'field1 = "GR"' when you type "GR" in the control
• 'field1 LIKE "GR*"' when you type "GR*" in the control
• 'field1 LIKE "GR*" or field1 like "BR*"' if you type 'LIKE "GR*" OR LIKE "BR*"' in the control

PS: if your "filter" controls on your form always have the same syntax (let's say "search_fieldName", where "fieldName" corresponds to the field in the underlying recordset) and are always located in the same zone (let's say formHeader), it is then possible to write a function that will automatically generate a filter for the current form. This filter can then be set as the form filter, or used for something else:

For each ctl in myForm.section(acHeader).controls
    if ctl.name like "search_"
        fld = myForm.recordset.fields(mid(ctl.name,8))
        if not isnull(ctl.value) then
           whereClause = whereClause & buildCriteria(fld.name ,fld.type, ctl.value) & " AND "
        endif
    endif
next ctl
if len(whereClause)> 0 then ...