Missing ), ], or Item in query expression error!!

Hi
I can't figure out why my Update / Select statment is not working The syntax for the sub query looks correct
Here is the code. This is driving me nuts, its probably something simple, but i can't see it Any help will be appreciated

uRecord = "UPDATE " & dLocation & " SET RepairTech ='" & Request.Form("rtechnicianListBox") & "'"
uRecord = uRecord & " WHERE ID IN(Select ID FROM InProcessQuality WHERE ID = " & Request.QueryString("record"))

It gives me this error



Microsoft JET Database Engine error '80040e14'

Missing ), ], or Item in query expression 'ID IN(Select ID FROM InProcessQuality WHERE ID = 226'. /repair.asp, line 39

why can't you just do this???

The reason why I was doing the sub query because RepairTech is in InProcessRepair table and ID is in InProcessQuality table.
Its not working because it doesn't know where ID is coming from and gives me the error. That is why I had to do the sub query

uRecord = "UPDATE " & dLocation & " SET RepairTech ='" & Request.Form("rtechnicianListBox") & "'"
uRecord = uRecord & " WHERE [InProcessQuality.ID] IN (SELECT [InProcessQuality.ID] FROM [InProcessQuality] WHERE [InProcessQuality.ID] = " & Request.QueryString("record"))



uRecord = "UPDATE " & dLocation & " Set RepairTech = '" & Request.Form("rtechnicianListBox") & "' WHERE [ID] = " & Request.QueryString("record")

Your database structure doesn't make sense.
You should never have to do that type of update, because they are unpredictable.

The table containing RepairTech should have a primary key, you should do the update based on that tables primary key.

I'm just confused on how I can restructure the tables. InProcessQuality can have many ordernumbers each with a different serialNumber. When I load the page for the Inprocessrepair it should show the data if the ordernumber and the serialnumber and the id from InProcessQuality to differentiate which record to show. that is why I am trying to select the * from InProcessRepair and InProcessQuality -> SerialNumber, OrderNumber and ID so i can update the table. Not sure if i am making any sense, haha its been a long day! can you tell

You should have a column in each table that links them together.

Hi Edmondj, try this please
uRecord = "UPDATE " & dLocation & " SET RepairTech ='" & Request.Form("rtechnicianListBox") & "'"
uRecord = uRecord & " WHERE ID IN(Select ID FROM InProcessQuality WHERE ID = " & Request.QueryString("record") & " )"


OR


uRecord = "UPDATE " & dLocation & " SET RepairTech ='" & Request.Form("rtechnicianListBox") & "'"
uRecord = uRecord & " WHERE ID IN(Select ID FROM InProcessQuality WHERE ID = '" & Request.QueryString("record") & "' )"

Cheers!