Delete records SQL syntax help

[Guest]

I'm so frutstrated. I've tried every possible way to write this delete
records code and I always get an error, either wrong syntax or data type
mismatch. Can anyone please help? Thanks much.

Dim Model As String
Model = [Forms]![Export_Ref_Parts]![ModelToImport]
ExcelARefParts = "ExcelARefParts"


DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * From ExcelARefParts WHERE [Model#] = & Model & ;"

 

[Douglas J Steele]

When referring to variables in SQL statements, the variables have to be
outside of the quotes so that you get the values they contain:

DoCmd.RunSQL "DELETE * From [" & ExcelARefParts & _
"] WHERE [Model#] = " & Model

This assumes Model# is a numeric field. If it's text, use:

DoCmd.RunSQL "DELETE * From [" & ExcelARefParts & _
"] WHERE [Model#] = '" & Model & "'"

Exagerated for clarity, that's


DoCmd.RunSQL "DELETE * From [" & ExcelARefParts & _
"] WHERE [Model#] = ' " & Model & " ' "

(BTW, I'd recommend not including special characters like # in field names)

 

[Guest]

The problems you are having is that everything is inside the quotes:

DoCmd.RunSQL "DELETE * From ExcelARefParts WHERE [Model#] = & Model & ;"

So, you are not getting the value in the variable, ExcelArefParts you are
getting the literal "ExcelArefParts". The rest of your statement is
meaningless to Jet. Try this if Model# is a numeric field.

Dim strSQL as String
strSQL = "DELETE * FROM ExcelARefParts WHERE [Model#] = " & _
[Forms]![Export_Ref_Parts]![ModelToImport]
CurrentDb.Execute(strSQL), dbFailOnError

If it is a text field, you need this syntax:

strSQL = "DELETE * FROM ExcelARefParts WHERE [Model#] = '" & _
[Forms]![Export_Ref_Parts]![ModelToImport] & "'"

Notice I used the Execute Method rather than the RunSQL. It is a lot faster
because it doesn't go through the Access User Interface, it goes directly to
Jet.
One other thing = # is a bad thing to use in naming any objects. The best
rule to use for naming is - Use only letters, digits, and the underscore _
Spaces and any special characters can be problematic.

I'm so frutstrated. I've tried every possible way to write this delete
records code and I always get an error, either wrong syntax or data type
mismatch. Can anyone please help? Thanks much.

Dim Model As String
Model = [Forms]![Export_Ref_Parts]![ModelToImport]
ExcelARefParts = "ExcelARefParts"


DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * From ExcelARefParts WHERE [Model#] = & Model & ;"