Delete records from table using code

[David McKnight]

What general code would I use if want to delete the records of a table based
on a certain field criteria, say delete from table [A] all records in which
[year] = 2005?

 

[Allen Browne]

Execute a Delete query statement.

Something like this:
Dim strSql As String
strSql = "DELETE FROM [A] WHERE [A].[Year] = 2005;"
dbEngine(0)(0).Execute strSql, dbFailOnError

 

[David McKnight]

This works great if a state year as a number in the cod as you have it. But
if I try to do dependent on a input field I get an error "missing parameter"

Year=txtYear.Value

Dim strSql As String

strSql = "DELETE FROM [A] WHERE [A].[Year] = Year;"
dbEngine(0)(0).Execute strSql, dbFailOnError

--
David McKnight

Execute a Delete query statement.

Something like this:
Dim strSql As String
strSql = "DELETE FROM [A] WHERE [A].[Year] = 2005;"
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What general code would I use if want to delete the records of a table
based
on a certain field criteria, say delete from table [A] all records in
which
[year] = 2005?

 

[David McKnight]

Anonther Question on this if I want a range of years? Something like:
beginYear=txtbeginYear.Value
endYear=txtEndYear.Value

Dim strSql As String

strSql = "DELETE FROM [A] WHERE [A].[Year] > beginYear < endYear;"
dbEngine(0)(0).Execute strSql, dbFailOnErrorDavid McKnight

???

This works great if a state year as a number in the cod as you have it. But
if I try to do dependent on a input field I get an error "missing parameter"

Year=txtYear.Value

Dim strSql As String

strSql = "DELETE FROM [A] WHERE [A].[Year] = Year;"
dbEngine(0)(0).Execute strSql, dbFailOnError

--
David McKnight

Execute a Delete query statement.

Something like this:
Dim strSql As String
strSql = "DELETE FROM [A] WHERE [A].[Year] = 2005;"
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What general code would I use if want to delete the records of a table
based
on a certain field criteria, say delete from table [A] all records in
which
[year] = 2005?

 

[Allen Browne]

Assuming you have text boxes named txtBeginYear and txtEndYear on your form,
you can concatenate those numbers into the string:

If (IsNull(Me.txtBeginYear) Or IsNull(Me.txtEndYear)) Then
MsgBox "Both years required."
Else
strSql = "DELETE FROM [A] WHERE [A].[Year] Between " & _
Me.txtBeginYear & " And " & Me.txtEndYear & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError
End If

BTW, I hope that Year is just an example here. There is a VBA function named
Year(), so it is not a good name for a field. In some contexts, Access can
misunderstand what it refers to.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Anonther Question on this if I want a range of years? Something like:
beginYear=txtbeginYear.Value
endYear=txtEndYear.Value

Dim strSql As String

strSql = "DELETE FROM [A] WHERE [A].[Year] > beginYear < endYear;"
dbEngine(0)(0).Execute strSql, dbFailOnErrorDavid McKnight

???

This works great if a state year as a number in the cod as you have it.
But
if I try to do dependent on a input field I get an error "missing
parameter"

Year=txtYear.Value

Dim strSql As String

strSql = "DELETE FROM [A] WHERE [A].[Year] = Year;"
dbEngine(0)(0).Execute strSql, dbFailOnError

--
David McKnight

Execute a Delete query statement.

Something like this:
Dim strSql As String
strSql = "DELETE FROM [A] WHERE [A].[Year] = 2005;"
dbEngine(0)(0).Execute strSql, dbFailOnError

message
What general code would I use if want to delete the records of a
table
based
on a certain field criteria, say delete from table [A] all records in
which
[year] = 2005?