CODEWITHSUNDEEP
sqlms-accessvbaruntime-error
trappertam
trappertam

Reputation: 9

Compile/Syntax Error: Run-time error 3075: Syntax error in query expression

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

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'));'.

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.

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

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

Upvotes: 0

Views: 482

Answers (2)

trappertam
trappertam

Reputation: 9

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:

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

Upvotes: 0

SunKnight0
SunKnight0

Reputation: 3351

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:

"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],"'","''")

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.

Upvotes: 1

Related Questions