sql - 返回部分匹配值的记录

Question

我试图让一个查询工作，它从表单控件中获取值(有时只是字符串的第一部分)。我遇到的问题是它只在输入完整字符串时返回记录。

即在姓氏框中，我应该可以输入 gr，它会显示

绿色
灰色的
格雷厄姆

但目前它没有提供任何内容，除非使用完整的搜索字符串。

有问题的表单上有 4 个搜索控件，它们仅在填写该框的情况下用于查询。

查询是:

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

有一个 Access 方法!

如果您在表单上有“过滤器”控件，为什么不使用 Application.buildCriteria 方法，这将允许您将过滤条件添加到字符串中，然后从该字符串中创建过滤器，并构建您的 WHERE即时条款？

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 将返回(例如):

'field1 = "GR"' 当您在控件中键入“GR”时

'field1 LIKE "GR*"' 当您在控件

中键入 "GR*" 时

'field1 LIKE "GR*" or field1 like "BR*"' 如果你在控件中输入 'LIKE "GR*" OR LIKE "BR*"'

PS:如果表单上的“过滤器”控件始终具有相同的语法(假设为“search_fieldName”，其中“fieldName”对应于基础记录集中的字段)并且始终位于同一区域(假设为 formHeader)，然后就可以编写一个函数来自动为当前表单生成一个过滤器。然后可以将此过滤器设置为表单过滤器，或用于其他用途:

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