Help needed with UPDATE and SELECT code (1 Viewer)

bpatrick

New member
Local time
Today, 13:51
Joined
Apr 19, 2009
Messages
3
Hi all,

I am trying to run an update query based on the top x records of a table. I have got the update function to work, but it updates all records as opposed to the top x.

Please see below code:

Code:
Dim strSQL As String
    strSQL = "UPDATE SectionStatus SET Position01=2 WHERE EXISTS "
    strSQL = strSQL & "(SELECT TOP " & Me.C01 & " SectionDesign, Position01, SectionNumber FROM SectionStatus WHERE Position01 = 1 ORDER BY SectionNumber) "
    CurrentDb.Execute strSQL, dbFailOnError

Any help would be greatly appreciated as it's driving me bonkers!!

Thanks

Ben
 

bpatrick

New member
Local time
Today, 13:51
Joined
Apr 19, 2009
Messages
3
Hi vbaInet,

Thanks for your reply. Changing the EXISTS to IN causes a runtime error 3075 Missing expression or operator in 'IN (SELECT.........'

If I change the WHERE EXISTS to IN then I get runtime error 3306 Subquery that can return more than one field. Use EXISTS reserved word in the main query's FROM clause or revise subquery to return only one field.

Any ideas?

Thanks for you help so far.

Ben
 

Users who are viewing this thread

Top Bottom