编译/语法错误:运行时错误3075:查询表达式中的语法错误

时间:2021-12-23 15:41:41

I am using Access 2016 VBA. All code works fine, otherwise.

我正在使用Access 2016 VBA。所有代码都正常,否则。

Public Function PopUp()

   Dim strSQL As String
   Dim rs As DAO.Recordset
   Dim db As DAO.Database

   strSQL = "SELECT PopUpReminders.*, PopUpReminders.ReminderCompletion, PopUpReminders.ReminderStartDate, PopUpReminders.Employee FROM PopUpReminders WHERE (((PopUpReminders.ReminderCompletion)=False) AND ((PopUpReminders.ReminderStartDate)<=Now() AND ((PopUpReminders.Employee)='" & Forms![Login]![txtUserName] & "'));"
   Set db = CurrentDb
   Set rs = db.OpenRecordset(strSQL)

   If rs.RecordCount = 0 Then
      'Do Nothing
   Else
      If rs.RecordCount > 0 Then
      Do
         DoCmd.OpenForm "SFPopUpReminder"
      Loop Until rs!ViewedRecord = True
      End If
   End If

   rs.Close
   Set rs = Nothing

End Function

The error that appears is (copied exactly)

出现的错误是(完全复制)

MS VB Run-time error 3075: Syntax error in query expression '(((PopUpReminders.ReminderCompletion)=False) And ((PopUpReminders.ReminderStartDate)<=Now() And ((PopUpReminders.Employee)='rerdeljac'));'.

MS VB运行时错误3075:查询表达式中的语法错误'(((PopUpReminders.ReminderCompletion)= False)和((PopUpReminders.ReminderStartDate)<= Now()和((PopUpReminders.Employee)='rerdeljac')); ”。

Please note, "rerdeljac" is the logintext entered into the textbox on Forms![Login]![txtUserName] and which was matched to PopUpReminders.Employee; please note also that the error message does not include the remainder of the SQL code.

请注意,“rerdeljac”是输入到Forms![Login]![txtUserName]上的文本框中的logintext,它与PopUpReminders.Employee匹配;请注意,错误消息不包括SQL代码的其余部分。

(PopUpReminders.Employee) is a field on a table filled only with text, and Forms![Login]![txtUserName] is a textbox on a form intended to be filled only with text characters.

(PopUpReminders.Employee)是一个只填充文本的表上的字段,而Forms![Login]![txtUserName]是表单上的文本框,仅用文本字符填充。

The error occurs on the Set rs = db.OpenRecordset(strSQL) line.

Set rs = db.OpenRecordset(strSQL)行发生错误。

2 个解决方案

#1


1  

Your statement needs one more ) right after Now() if I am counting right. Your SQL statement is overly complicated (probably because you copied it from a query you made using the GUI). This is sufficient:

如果我算数正确的话,你的声明需要在Now()之后。您的SQL语句过于复杂(可能是因为您从使用GUI创建的查询中复制了它)。这就足够了:

"SELECT * FROM PopUpReminders WHERE ReminderCompletion=False AND ReminderStartDate<=Now() AND Employee='" & Forms![Login]![txtUserName] & "'"

This will fail if one of your users decides to type a ' (single quote) in txtUserName. You should at least change it to Replace(Forms![Login]![txtUserName],"'","''")

如果您的某个用户决定在txtUserName中键入'(单引号),则会失败。您至少应该将其更改为Replace(Forms![Login]![txtUserName],“'”,“''”)

Also RecordCount is not reliable. You should use rs.EOF=False OR rs.BOF=False to check if any records were returned and iterate through them with rs.MoveFirst and rs.MoveNext.

RecordCount也不可靠。您应该使用rs.EOF = False或rs.BOF = False来检查是否返回了任何记录,并使用rs.MoveFirst和rs.MoveNext迭代它们。

#2


0  

Actually, it was a combination of Fionnuala's removal of column names and SunKnight0's addition of the parentheses after the Now() that cured the issue. I can't put the answer to both, and SunKnight went way above and beyond so he gets the mark. Here is the corrected code, for those who might need it in the future:

实际上,这是Fionnuala删除列名和SunKnight0在Now()之后添加括号的组合,它解决了这个问题。我无法回答这两个问题,并且SunKnight超越了所以他得到了标记。以下是更正的代码,适用于将来可能需要它的人:

strSQL = "SELECT PopUpReminders.* FROM PopUpReminders WHERE (((PopUpReminders.ReminderCompletion)=False) AND ((PopUpReminders.ReminderStartDate)<=Now()) AND ((PopUpReminders.Employee)='" & Forms![Login]![txtUserName] & "'));"

#1


1  

Your statement needs one more ) right after Now() if I am counting right. Your SQL statement is overly complicated (probably because you copied it from a query you made using the GUI). This is sufficient:

如果我算数正确的话,你的声明需要在Now()之后。您的SQL语句过于复杂(可能是因为您从使用GUI创建的查询中复制了它)。这就足够了:

"SELECT * FROM PopUpReminders WHERE ReminderCompletion=False AND ReminderStartDate<=Now() AND Employee='" & Forms![Login]![txtUserName] & "'"

This will fail if one of your users decides to type a ' (single quote) in txtUserName. You should at least change it to Replace(Forms![Login]![txtUserName],"'","''")

如果您的某个用户决定在txtUserName中键入'(单引号),则会失败。您至少应该将其更改为Replace(Forms![Login]![txtUserName],“'”,“''”)

Also RecordCount is not reliable. You should use rs.EOF=False OR rs.BOF=False to check if any records were returned and iterate through them with rs.MoveFirst and rs.MoveNext.

RecordCount也不可靠。您应该使用rs.EOF = False或rs.BOF = False来检查是否返回了任何记录,并使用rs.MoveFirst和rs.MoveNext迭代它们。

#2


0  

Actually, it was a combination of Fionnuala's removal of column names and SunKnight0's addition of the parentheses after the Now() that cured the issue. I can't put the answer to both, and SunKnight went way above and beyond so he gets the mark. Here is the corrected code, for those who might need it in the future:

实际上,这是Fionnuala删除列名和SunKnight0在Now()之后添加括号的组合,它解决了这个问题。我无法回答这两个问题,并且SunKnight超越了所以他得到了标记。以下是更正的代码,适用于将来可能需要它的人:

strSQL = "SELECT PopUpReminders.* FROM PopUpReminders WHERE (((PopUpReminders.ReminderCompletion)=False) AND ((PopUpReminders.ReminderStartDate)<=Now()) AND ((PopUpReminders.Employee)='" & Forms![Login]![txtUserName] & "'));"