查询表达式中的语法错误（缺少运算符）

I am trying to create a search form in MS Access 2013, each time i insert the search criteria in any of the fields created i get the error message

我想在MS Access 2013中创建一个搜索表单,每次我在创建的任何字段中插入搜索条件时都会收到错误消息

"syntax error (missing operator) in query expression "*FROMWHERE (FirstName) like "Godswill""AND"

“查询表达式中的语法错误(缺少运算符)”* FROMWHERE(FirstName),如“Godswill”“AND”

find below the codes

找到下面的代码

<blink>
Private Sub cmdSearch_Click()
On Error GoTo errr
Me.qryCandInfo_subform.Form.RecordSource = "SELECT*FROM" & BuildFilter
Me.qryCandInfo_subform.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"
Const conSetDate = "\#dd\/mm\/yyyy\#"
varWhere = Null

If Me.txtEnrNo > "" Then
varWhere = varWhere & "(Enr_No) like " & Me.txtEnrNo & "AND"
End If

If Me.txtFirstName > "" Then
varWhere = varWhere & "(First_Name) like " & tmp & Me.txtFirstName & tmp & "AND"
End If

If Me.txtDateFrom > "" Then
varWhere = varWhere & "((Admisssion_Year)>= " & Format(Me.txtDateFrom, conSetDate) & ") AND"
End If

If Me.txtDateTo > "" Then
varWhere = varWhere & "((Admisssion_Year)<= " & Format(Me.txtDateTo, conSetDate) & ") AND"
End If

If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE" & varWhere

If Right(varWhere, 5) = "AND" Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function

1 个解决方案

#1

There is a lot wrong with your code.
To make it easy for you and everybody involved: Add spaces before and after every keyword:

你的代码有很多问题。为了方便您和所涉及的每个人:在每个关键字之前和之后添加空格:

"SELECT*FROM" & BuildFilter => "SELECT * FROM " & BuildFilter
& "AND" => & " AND "
varWhere = "WHERE" & varWhere => varWhere = " WHERE " & varWhere
& ") AND" => & ") AND "

Additionally you are missing to specfify the table you want to select from...
Furthermore your SQL has a trailing AND. Either take care of that or choose the not so perfect approch of appening a 1: varWhere = " WHERE " & varWhere & " 1". (Your version checks for ending with some 5 characters and compare with And - that should NEVER be true...)

另外,您缺少指定要从中选择的表...此外,您的SQL具有尾随AND。要么照顾它,要么选择不那么完美的approch来附加1:varWhere =“WHERE”&varWhere&“1”。 (你的版本检查以5个字符结尾并与And比较 - 这绝不应该是真的......)

Furthermore: SQL-injection... I doubt that pre- and appening """" changes anything

此外:SQL注入...我怀疑pre-and appening“”“”会改变任何东西

#1