I am trying to get a conditional UPDATE SQL statement to update an Access database but, every time, it updates the second SQL statement and not the first.?

Here is the SQL statement:

strSQL7 = "SELECT * FROM tblDevices WHERE DeviceID_ID = '" & Session("DeviceName") & "'"

objARec.Open strSQL7, strConnDataData, adOpenForwardOnly, adLockOptimistic

If objARec("DeviceStatus")="Out of Service" then
    strSQL2 = "UPDATE tblDevices SET DeviceStatus='Out of Service' WHERE DeviceID_ID = '" & Session("DeviceName") & "'"
    objAConn.Execute strSQL2

Else
    strSQL2 = "UPDATE tblDevices SET DeviceStatus='Needs-Service' WHERE DeviceID_ID = '" & Session("DeviceName") & "'"
    objAConn.Execute strSQL2

end if


Any ideas? Thanks.

Are you sure objARec("DeviceStatus") has any value at all? Try doing...

    response.write objARec("DeviceStatus")

above your if/else and see what you get.

In this particular case yes, because I have actually set ("DeviceStatus") to 'Out of Service' in the full script. This particular part for the conditional SQL is just a part of the whole script, which is long but I will post it if need be.

That's fine if you have a variable called device status in your script, but you are querying it out of the recordset as well and that is the value I was mentioning. How about trying something like this...

rs = server.createobject("adodb.recordset")
sql = "select * from tblDevices where DeviceId_Id = '" & Session("DeviceName") & "'"
rs.open sql, strConnDataData, 3, 3
if not rs.eof then
    if rs("DeviceStatus") = "Out of Service" then
        rs("DeviceStatus") = "Out of Service"
    else
        rs("DeviceStatus") = "Needs Service"
    end if
        rs.update
end if

QUESTION?
If DeviceStatus is already "Out of Service" then why are you trying to update it as "Out of Service"?