Syntax Error while defining Date Variable

[Firasath]

Hi,

I am picking up a date from the form in VBA Code and trying to execute SQL Statement. I am getting "Run Time Error "3075". Syntax Error (missing operator) in query expression". Please find below the respective Code.

Private Sub btnDeldt_Click()

Dim dt As Date

dt = Me.txtDelDt

Dim sql As String
sql = " Delete tblCompDB.ActualClosedDate WHERE (((tblCompDB.ActualClosedDate)>= #" & dt & "# "
DoCmd.RunSQL sql

sql = " Update tblCompDB.ComplaintStatus = 'Pending' WHERE (((tblCompDB.ActualClosedDate)>= #" & dt & "# "
DoCmd.RunSQL sql

End Sub

Kindly suggest.

 

[Norie]

I don't think the date is the problem.

The DELETE statement should look like this.

sql = " Delete tblCompDB WHERE tblCompDB.ActualClosedDate>= #" & dt & "# "

The UPDATE statement should look like this.

sql = " UPDATE tblCompDB SET ComplaintStatus = 'Pending' WHERE tblCompDB.ActualClosedDate>= #" & dt & "# "

 

[Firasath]

Thanks. But it is giving the same error.

I think even if it is get executed it might delete the whole records wherever date is more than specified criteria. My purpose is to remove the date only from ActualClosedDate field.

 

[Norie]

If you just want to delete a field value use an UPDATE query and set the field to an empty string.

 

[Firasath]

If you just want to delete a field value use an UPDATE query and set the field to an empty string.

Thanks. I will try and update you.

 

[Firasath]

As per your advised Nourie, I have changed the query as below.

Private Sub btnDelDt_Click()

Dim sql As String
Dim dt As Date

dt = Me.txtDelDt

sql = " Update tblCompDB " _
        & " Set ActualClosedDate = "", ComplaintStatus = 'Pending' " _
        & " where ActualClosedDate >= #" & dt & "# "

DoCmd.RunSQL sql

End Sub

Now I am getting "Run Time Error '3075': Syntax Error in string in query expression.... " error. Any advise.

 

[Firasath]

Could it be because I have the date format "dd/mm/yyyy h\:nn\:ss AM/PM" in the Table and in the form I am selecting Short Date for the Text Box.

 

[Norie]

I don't think the date format would cause a syntax error, more likely the results of the queries would be incorrect.

Have you tried printing your SQL to the Immediate Window using Debug.Print, copying it and running it manually?

 

[Firasath]

I will try that and update you.

In the meantime, I have made it work by creating a Query in Access.

 

[james_lankford]

Private Sub btnDelDt_Click()


Dim sql As String
Dim dt As Date


dt = Me.txtDelDt


sql = " Update tblCompDB " _
        & " Set ActualClosedDate = "", ComplaintStatus = 'Pending' " _
        & " where ActualClosedDate >= #" & dt & "# "


DoCmd.RunSQL sql


End Sub

I think the problem is
Set ActualClosedDate = ""
I'm surprised it runs at all


wouldn't the first double quote there
close this string
& " Set ActualClosedDate = "


try this

Private Sub btnDelDt_Click()


Dim sql As String
Dim dt As Date


dt = Me.txtDelDt


sql = " Update tblCompDB " _
        & " Set ActualClosedDate = null, ComplaintStatus = 'Pending' " _
        & " where ActualClosedDate >= #" & dt & "# "


DoCmd.RunSQL sql


End Sub