Delete SQL statement

T

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

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

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

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

John W. Vinson

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.
Click to expand...

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?
 
T

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?
 
T

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)
 
J

John W. Vinson

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?
Click to expand...

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?
 
J

John W. Vinson

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)
Click to expand...


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
 
T

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)
 
T

tracktraining

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

if elseif help 16
insert values into table 12
3134 error 2
Help with code --> Invlaid use of Null???? 2
Posting a dataset to a SQL table using VBA 0
Running SQL statements in VBA 4
Annoying Data Type Conversion Error 1
query to textbox 1

Top