我正在尝试让查询工作,该查询从表单控件获取值(有时只是字符串的第一部分)。我遇到的问题是,它仅在输入完整字符串时返回记录。
即在姓氏框中,我应该能够输入 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;
有一个访问方法!
如果表单上有“过滤器”控件,为什么不使用 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 ...
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)