Problem with SQL (delete)

[faz88]

Hi, i am having a problem with the statement below.

StrSQL = "DELETE * FROM tbltreatment WHERE treatmentid=" _
& Me.lstthreading.Value & ";"
CurrentDb.Execute StrSQL

The error message i get is syntax error(missing operator) in query expression 'treatmentdid='.


any help is much appreciated

 

[gemma-the-husky]

try

where treatmentid = " _

the spaces may be required. sql is touchy about spaces

may also be the _ & syntax

normal its the other way for a line continuation

 

[faz88]

try

where treatmentid = " _

the spaces may be required. sql is touchy about spaces

may also be the _ & syntax

normal its the other way for a line continuation

i tried your suggestions. The error message doesnt show now, but the selected record still does not delete

 

[pbaldy]

Add this right before the Execute line and post the result from the Immediate window here:

Debug.Print StrSQL

 

[Banana]

An alternative which also will cause less bloat in the database and sidestep the whole hassle of delimiting & concatenating:

Create a Delete query and use a parameter.

Query's SQL:

PARAMETERS p TEXT(255);
DELETE FROM tbltreatment WHERE treatmentid = [p];

In VBA:

With CurrentDb.QueryDefs("DeleteTreatmentID")
   .Parameters("p") = Me.lstthreading
   .Execute dbFailOnError
End With