VB表单和MS Access SQL通配符搜索 [英] VB Form and MS Access SQL Wildcard Search

问题描述

我的表表1 具有3个字段: FName ， LName ， Phone .我正在使用Microsoft Access 2010运行SQL查询.有些行的电话值为空/空.

My table table1 has 3 fields: FName, LName, Phone. I am using Microsoft Access 2010 for running the SQL query. Some rows has empty / null phone values.

我有一个VB表格，可以接受搜索参数.用户可以输入 (FName and LName) 或 (Phone) ，但不能同时输入两者.

I have a VB form which accepts search parameters. A user can enter (FName and LName) or (Phone), but not both at the same time.

当我尝试时:

SELECT table1.LName, table1.FName, table1.Phone FROM table1 WHERE table1.LName Like ('*' & Forms!frmSearchMain!LName & '*') AND table1.FName Like ('*' & Forms!frmSearchMain!FName & '*') ORDER BY table1.LName, table1.FName;

SELECT table1.LName, table1.FName, table1.Phone FROM table1 WHERE table1.LName Like ('*' & Forms!frmSearchMain!LName & '*') AND table1.FName Like ('*' & Forms!frmSearchMain!FName & '*') ORDER BY table1.LName, table1.FName;

它为我提供了一个与给定(FName和LName)参数匹配的用户列表.效果很好.

It gives me a list of user matching the given (FName and LName) parameters. It works fine.

类似地，当我尝试:

SELECT table1.LName, table1.FName, table1.Phone FROM table1 WHERE table1.Phone Like ('*' & Forms!frmSearchMain!Phone & '*') ORDER BY table1.LName, table1.FName;

SELECT table1.LName, table1.FName, table1.Phone FROM table1 WHERE table1.Phone Like ('*' & Forms!frmSearchMain!Phone & '*') ORDER BY table1.LName, table1.FName;

它为我提供了与给定(Phone)参数匹配的用户列表.它也可以正常工作.

It gives me a list of user matching the given (Phone) parameter. It works fine too.

但是，当我结合这两个查询时:

But, when I combine both these queries:

SELECT table1.LName, table1.FName, table1.Phone FROM table1 WHERE table1.Phone Like ('*' & Forms!frmSearchMain!Phone & '*') AND table1.LName Like ('*' & Forms!frmSearchMain!LName & '*') AND table1.FName Like ('*' & Forms!frmSearchMain!FName & '*') ORDER BY table1.LName, table1.FName;

SELECT table1.LName, table1.FName, table1.Phone FROM table1 WHERE table1.Phone Like ('*' & Forms!frmSearchMain!Phone & '*') AND table1.LName Like ('*' & Forms!frmSearchMain!LName & '*') AND table1.FName Like ('*' & Forms!frmSearchMain!FName & '*') ORDER BY table1.LName, table1.FName;

它没有给我预期的结果.

It doesn't give me the expected results.

我甚至尝试了在(FName和LName)和(Phone)搜索参数之间的OR条件.它不起作用.我已经尝试对所有搜索参数使用nz(frmSearchMain!Phone,"")，而不是*，但是到目前为止没有运气.

I even tried OR condition between (FName and LName) and (Phone) search parameters. It doesn't work. I have tried nz(frmSearchMain!Phone,"") for all the search params, % instead of *, but no luck so far.

我是MS Access SQL查询格式的新手.我已经在MySQL中多次使用这种类型的查询.但是我无法从这一结果中得到预期的结果.

I am new to the MS Access SQL Query format. I have used this type of queries a lot of times in MySQL. But I couldn't get the expected results from this one.

任何帮助将不胜感激.

推荐答案

我认为，如果我们简化它，应该更容易将其分类.因此，我现在决定立即忽略LName，而仅基于FName或Phone进行搜索.

I think it should be easier to sort this one out if we simplify it. So I arbitrarily decided to ignore LName for now, and do the searching based only on FName or Phone.

在我的frmSearchMain版本中，我将保存FName的搜索目标值的文本框命名为txtSearchFName，这仅仅是因为我更喜欢将控件的名称与记录源字段的名称不同.同样，我在Phone搜索目标文本框中选择了txtSearchPhone.

In my version of frmSearchMain, I named the text box which holds the search target value for FName as txtSearchFName simply because I prefer to name the control differently than the record source field. Similarly, I chose txtSearchPhone for the Phone search target text box.

由于我已经有一个名为Table1的表，所以我将其命名为tblKeerthiram.

Since I already had a table named Table1, I called mine tblKeerthiram instead.

有了这些更改，此查询为我提供了与txtSearchFName或txtSearchPhone相匹配的行.如果txtSearchFName和txtSearchPhone都为Null，则查询返回表中的所有行...这就是我希望的结果.

With those changes, this query gives me rows which match txtSearchFName or txtSearchPhone. If both txtSearchFName and txtSearchPhone are Null, the query return all rows from the table ... which is what I hope you want.

SELECT
    t1.id,
    t1.FName,
    t1.Phone
FROM tblKeerthiram AS t1
WHERE
           (t1.FName Like "*"
               & [Forms]![frmSearchMain]![txtSearchFName]
               & "*"
        OR [Forms]![frmSearchMain]![txtSearchFName] Is Null)
    AND
           (t1.Phone Like "*"
               & [Forms]![frmSearchMain]![txtSearchPhone]
               & "*"
        OR [Forms]![frmSearchMain]![txtSearchPhone] Is Null)
ORDER BY t1.FName;

此方法的结果是，如果txtSearchFName和txtSearchPhone都不为Null，则查询将仅返回匹配两者的行.据我了解您的描述，这不是您想要的.您只想搜索一个或多个，而不要同时搜索.

A consequence of this approach is that, if both txtSearchFName and txtSearchPhone are non-Null, the query will return only the rows which match both. As I understood your description, that is not what you want. You want to search by only one or the other, not both at the same time.

在这种情况下，建议您在两个搜索文本框中使用更新后事件来在其中一个输入非null值时设置另一个null.我不确定该句子的清晰程度，因此只需将此代码添加到表单的模块中即可.它将确保两个文本框中只有一个包含非Null值.

In that case I suggest you use the after update events for the two search text boxes to set the other Null when a non-Null value has been entered into one of them. I'm unsure how clear that sentence was, so just add this code to your form's module. It will ensure that only one of the two text boxes will contain a non-Null value.

Option Compare Database
Option Explicit

Private Sub txtSearchFName_AfterUpdate()
    If Not IsNull(Me.txtSearchFName) Then
        Me.txtSearchPhone = Null
    End If
End Sub

Private Sub txtSearchPhone_AfterUpdate()
    If Not IsNull(Me.txtSearchPhone) Then
        Me.txtSearchFName = Null
    End If
End Sub

最后，如果所有方法都奏效，那么您只需对其进行修改即可处理LName.希望那部分不会太艰巨.祝你好运.

Finally if that all works, then you only need to revise it to deal with LName. Hope that part won't be too daunting. Good luck.

这篇关于VB表单和MS Access SQL通配符搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！