Delete Access records based on multiple criteria 2

[ksee2033]

Hello,

I am new to the forum and an extreme newbie when it comes to using code to manipulate a database, so please be patient!

I am trying to delete records from an Access database, but only records that have 3 specific columns that are identical. For instance, if the Date column has today's date, and the Reason column has a particular value (string), and the Response column has a particular value (string), then and only then should those records be deleted. Here is my weak attempt at accomplishing this:

'Delete all records matching criteria
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
oConn.connectionstring = "DRIVER={Microsoft Access Driver (*.mdb)}"
oConn.connectionstring = oConn.connectionstring & "; DBQ=" & DSNname
oConn.Open<br>

SQLstr = "DELETE * FROM Arata_pressure_test_data WHERE Date = Date() AND [Who is at fault] = Request("AtFault") AND [Reject reason] = Request("RejReason")"

oRS.Open SQLstr, oConn, 2, 3

Like I said, I'm a newbie...Thanks for all your help!

 

[RoyVidar]

Hi!

You'll need to concatinate the values into the sql string, not the names/references.

[tt]SQLstr = "DELETE * FROM Arata_pressure_test_data WHERE Date = #" & format(Date,"yyyy-mm-dd") & "# AND [Who is at fault] = '" & Request("AtFault") & "' AND [Reject reason] = " & Request("RejReason")[/tt]

Here, using date qualifiers on the date (#), and applying ansi format (for safety), text qualifiers on the AtFault, and none at the reject reason - which is what to do on numerics.

Perhaps also look into the execute method of the connection object (and parameters), this recordset isn't meant for opening;-)

[tt]oConn.Execute SQLstr [/tt]

Hope this is nearer

Roy-Vidar

 

[Golom]

There are a few issues here.

First ... you are building an SQL statement in code ... perfectly acceptable but your use of double quotes may be a problem. You need to double up quotes if you want a quote to appear in the output string.
You need something like
[tt]
SQLstr = "DELETE * FROM Arata_pressure_test_data WHERE [Date] = Date() AND [Who is at fault] = Request(""AtFault"") AND [Reject reason] = Request(""RejReason"")"
[/tt]

Second ... as written this will invoke a function called "Request" twice ... once with the argument "AtFault" and once with the argument "RejReason". What is that function and how does it know what to return in each case?

Third ... "Date" is a reserved word (it is the name of a builtin function). If you have a field name "Date" then it should appear as [Date]. The Date function should be written as Date().

 

[ksee2033]

Excellent RoyVidar...it's working now, thank you very much!

Golom, you are correct about the "Date" field in my database...I am will change that asap.