Delete SQL statement

[tracktraining]

Hi All,

I have the following code. When I compile it, the code gives no error. But
when I run the form, I get an error message "Syntax error in FROM clause". I
only copy a section of the code.

other code missing.....
LastRevision = rs!DateAssigned
NowRevision = Now()
DaysDiff = DateDiff("d", LastRevision, NowRevision)
MsgBox DaysDiff
MsgBox Me.txtDocID
strDEL = "DELETE FROM EmpDocStatus" & _
"WHERE (EmpDocStatus.EmpEmail = rs!EmpEmail AND" & _
"EmpDocStatus.DocID = Me.txtDoc AND " & _
"EmpDocStatus.DateCompleted = Null AND" & _
"DaysDiff <='0');"
CurrentDb().Execute strDEL

more code missing....

Can anyone tell me what is wrong with my DELETE statement?

Thanks,
Tracktraining.
--
Learning

[Pendragon]

You need to have spaces before your end quotes. Also, I see that you've
included VBA language in your SQL statement. Note the single quote - double
quote after the equals sign and before the AND as the email is text and no
single quote for the DocID (assuming you are using a number/autonumber field
from Me.txtDoc).

strDEL = "DELETE FROM EmpDocStatus " & _

"WHERE (EmpDocStatus.EmpEmail = '" & rs!EmpEmail & "')
AND " & _
"EmpDocStatus.DocID = " & Me.txtDoc & " AND " & _

"EmpDocStatus.DateCompleted = Null AND " & _
"DaysDiff <='0');"

Also, if DaysDiff is returning a numeric value, you don't want the single
quotes around the zero.

Not sure what the beginning of your code is doing, but here's one other
thing. If your recordset is returning a single value (rs!EmpEmail), then
your Delete SQL should be okay. If the recordset is multiple records, you
might consider simply deleting the data for the specific fields as you scroll
through the recordset using a Do Loop and changing your recordset to grab the
filtered records.

[tracktraining]

Hi Pendragon,

I copy and paste your code, with minor changes (the me.txtdocid is a text).
See code below:

strDEL = "DELETE FROM EmpDocStatus " & _
"WHERE (EmpDocStatus.EmpEmail = '" & rs!EmpEmail & "')
AND " & _

"EmpDocStatus.DocID = '" & Me.txtDocID & "' AND " & _

"EmpDocStatus.DateCompleted = Null AND " & _

"DaysDiff = 0;"

Now I am getting "Too few parameters. Expected 1" error message.

Can you help?

Thanks.
--
Learning

[tracktraining]

I also changed EmpDocstatus.DateCompleted = Null to
EmpDocStatus.DateCompleted IS NULL

And
DaysDiff = ..... to ...... DaysDiff <= 0 ..... but I am still getting the
too few parameters. Expected 1 error.

more codes.....

If rs!Revision <> Me.txtRev Then
MsgBox "delete old revision record"
LastRevision = rs!DateAssigned
NowRevision = Date

DaysDiff = DateDiff("d", LastRevision, NowRevision)
'MsgBox DaysDiff

'MsgBox rs!Revision

strDEL = "DELETE FROM EmpDocStatus " & _

"WHERE EmpDocStatus.EmpEmail = '" & rs!EmpEmail & "'
AND " & _

"EmpDocStatus.DocID = '" & Me.txtDocID & "' AND " & _

"EmpDocStatus.Revision = '" & rs!Revision & "' AND " & _
"EmpDocStatus.DateCompleted IS NULL AND " & _
"DaysDiff <= 0;"
dbdel.Execute (strDEL)
End If

more codes.....

Any help/suggestion would be much appreciated.
--
Learning

[John W. Vinson]

This suggests that one or more of rs!EmpEmail, Me.txtDocID or rs!Revision is
null. If you step through the code and view the value of strDel in the
Immediate window... what does it contain?
--

John W. Vinson [MVP]

[tracktraining]

This is what I get:

DELETE FROM EmpDocStatus WHERE EmpDocStatus.EmpEmail = 'mmills' AND
EmpDocStatus.DocID = '001-0001-000' AND EmpDocStatus.Revision = 'AC' AND
EmpDocStatus.DateCompleted IS NULL AND DaysDiff <= 0;

I think there is something wrong with the EmpDocStatus.DateCompleted IS NULL?

--
Learning

[tracktraining]

Ok ... I understand the problem now after looking at the stuff from the
Immediate window. It is my DaysDiff. I know that DaysDiff is holding the
correct number (i.e. Msgbox DaysDiff) but it is not getting that number in
the sql ... so that means my syntax is wrong? Please help .......

If rs!Revision <> Me.txtRev Then
MsgBox "delete old revision record"
LastRevision = rs!DateAssigned
NowRevision = Date
DaysDiff = DateDiff("d", LastRevision, NowRevision)
MsgBox DaysDiff
'MsgBox rs!Revision
strDEL = "DELETE FROM EmpDocStatus " & _
"WHERE EmpDocStatus.EmpEmail = '" & rs!EmpEmail & "'
AND " & _
"EmpDocStatus.DocID = '" & Me.txtDocID & "' AND " & _
"EmpDocStatus.Revision = '" & rs!Revision & "' AND " & _
"EmpDocStatus.DateCompleted IS NULL AND " & _
"DaysDiff <= 0;"

Debug.Print strDEL
dbdel.Execute (strDEL)

[John W. Vinson]

On Tue, 7 Oct 2008 15:39:02 -0700, tracktraining
<trackt...@discussions.microsoft.com> wrote:

>This is what I get:
>
>DELETE FROM EmpDocStatus WHERE EmpDocStatus.EmpEmail = 'mmills' AND
>EmpDocStatus.DocID = '001-0001-000' AND EmpDocStatus.Revision = 'AC' AND
>EmpDocStatus.DateCompleted IS NULL AND DaysDiff <= 0;
>
>I think there is something wrong with the EmpDocStatus.DateCompleted IS NULL?

No, that looks legit. Possibly the fieldnames in the query don't match those
in the table? Are any of the fields <yuck, PTOOOIE!> Lookup Fields? What is
DaysDiff - is it a table field, or an expression from somewhere else?

[John W. Vinson]

On Tue, 7 Oct 2008 16:03:09 -0700, tracktraining
<trackt...@discussions.microsoft.com> wrote:

>Ok ... I understand the problem now after looking at the stuff from the
>Immediate window. It is my DaysDiff. I know that DaysDiff is holding the
>correct number (i.e. Msgbox DaysDiff) but it is not getting that number in
>the sql ... so that means my syntax is wrong? Please help .......
>
> If rs!Revision <> Me.txtRev Then
> MsgBox "delete old revision record"
> LastRevision = rs!DateAssigned
> NowRevision = Date
> DaysDiff = DateDiff("d", LastRevision, NowRevision)
> MsgBox DaysDiff
> 'MsgBox rs!Revision
> strDEL = "DELETE FROM EmpDocStatus " & _
> "WHERE EmpDocStatus.EmpEmail = '" & rs!EmpEmail & "'
>AND " & _
> "EmpDocStatus.DocID = '" & Me.txtDocID & "' AND " & _
> "EmpDocStatus.Revision = '" & rs!Revision & "' AND " & _
> "EmpDocStatus.DateCompleted IS NULL AND " & _
> "DaysDiff <= 0;"
> Debug.Print strDEL
> dbdel.Execute (strDEL)

Aha. You can't put a VBA variable in a SQL statement - SQL knows nothing about
variables. It sounds like you simply don't want to run the query at all if
DaysDiff is negative! Try

If rs!Revision <> Me.txtRev Then
MsgBox "delete old revision record"
LastRevision = rs!DateAssigned
NowRevision = Date
DaysDiff = DateDiff("d", LastRevision, NowRevision)
MsgBox DaysDiff

If DaysDiff > 0 Then

'MsgBox rs!Revision
strDEL = "DELETE FROM EmpDocStatus " & _
"WHERE EmpDocStatus.EmpEmail = '" & rs!EmpEmail & "'
AND " & _
"EmpDocStatus.DocID = '" & Me.txtDocID & "' AND " & _
"EmpDocStatus.Revision = '" & rs!Revision & "' AND " & _
"EmpDocStatus.DateCompleted IS NULL AND " & _
"DaysDiff <= 0;"
Debug.Print strDEL
dbdel.Execute (strDEL)

End If

[tracktraining]

Is it my DaysDiff? I know that DaysDiff is holding the

correct number (i.e. Msgbox DaysDiff) but it is not getting that number in
the sql ... so that means my syntax is wrong?

DaysDiff is the difference in lastrevision date and nowrevision date.

Please help .......

If rs!Revision <> Me.txtRev Then
MsgBox "delete old revision record"
LastRevision = rs!DateAssigned
NowRevision = Date
DaysDiff = DateDiff("d", LastRevision, NowRevision)
MsgBox DaysDiff
'MsgBox rs!Revision
strDEL = "DELETE FROM EmpDocStatus " & _
"WHERE EmpDocStatus.EmpEmail = '" & rs!EmpEmail & "'
AND " & _
"EmpDocStatus.DocID = '" & Me.txtDocID & "' AND " & _
"EmpDocStatus.Revision = '" & rs!Revision & "' AND " & _
"EmpDocStatus.DateCompleted IS NULL AND " & _
"DaysDiff <= 0;"

Debug.Print strDEL
dbdel.Execute (strDEL)

> DELETE FROM EmpDocStatus WHERE EmpDocStatus.EmpEmail = 'mmills' AND
> EmpDocStatus.DocID = '001-0001-000' AND EmpDocStatus.Revision = 'AC' AND
> EmpDocStatus.DateCompleted IS NULL AND DaysDiff <= 0;

--
Learning

[tracktraining]

Thank you so much!... now i learn something new as well. SQL don't understand
variables......

--
Learning