返回与值部分匹配的记录 [英] Returning records that partially match a value

问题描述

我试图使查询工作从表单控件获取值(有时只是字符串的第一部分).我的问题是，仅当输入完整字符串时，它才返回记录.

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.

即在姓氏框中，我应该可以键入gr，它会弹出

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.

有问题的表单上有4个搜索控件，并且只有在填写方框的情况下，它们才会在查询中使用.

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

查询是:

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;

推荐答案

有一种访问方法！

如果表单上有过滤器"控件，为什么不使用Application.buildCriteria方法，该方法将允许您将过滤条件添加到字符串中，然后从该字符串中进行过滤，并且快速构建您的WHERE子句?

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

buildCriteria将返回(例如):

the buildCriteria will return (for example):

• 'field1 = "GR"'当您在控件中键入"GR"时
在控件中键入"GR*"时
• 'field1 LIKE "GR*"'
• 'field1 LIKE "GR*" or field1 like "BR*"'(如果在控件中键入'LIKE "GR*" OR LIKE "BR*"'

• '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:如果表单上的过滤器"控件始终具有相同的语法(例如，"search_fieldName"，其中"fieldName"对应于基础记录集中的字段)，并且始终位于同一区域中(例如formHeader)，然后可以编写一个函数，该函数将自动为当前表单生成一个过滤器.然后可以将此过滤器设置为表单过滤器，或用于其他用途:

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

这篇关于返回与值部分匹配的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！