update query not finding records 1

[hondaman2003]

Can anyone see why this command will not work? The problem is the query doesn't find any records.

DoCmd.RunSQL "UPDATE [Patient table] SET [Owner2] = '" & [LAN ID - 1] & "' WHERE [Patient table].[Owner2] IN (SELECT TOP 2 [Patient Table].Owner2 FROM [Patient Table] WHERE ((([Patient Table].Owner2) Is Null Or ([Patient Table].Owner2)='') AND (([Patient Table].Completed2)=0)));"

The highlevel over view of this database is, people work records. We assign out records to be worked based on there "LAN ID". We have a form that will only show records where the LAN ID match who they are. The point to this command is to assign the LAN ID to the field 'Owner' in a certain number of records. The way it assigns records is if the 'Owner' field on a table is blank and the 'Completed' check box is not checked. On the form there is a field called 'LAN ID - 1' that is to be assigned to a certain number of records. That number of records is determined by the number '2' I hard coded into the command. I will change that so it pulls from the form.

When I pull the subquery out and run it on it's own, it will find the correct number of records. When I save the subquery as it's own query, then run the update query, it update the correct number of records. I can even run this entire command as a query and it will work. It will just not work if I run it in VBA.

 

[dhookom]

I always create a string variable to store the SQL. This allows me to more easily troubleshoot problem queries.

Dim strSQL as String
strSQL = "UPDATE [Patient table] SET [Owner2] = '" & [LAN ID - 1] & _
  "' WHERE [Patient table].[Owner2] IN (SELECT TOP 2 [Patient Table].Owner2 FROM [Patient Table] " & _
  "WHERE ((([Patient Table].Owner2) Is Null Or ([Patient Table].Owner2)='') AND (([Patient Table].Completed2)=0)));"
Debug.Print "strSQL: " & strSQL
Docmd.RunSQL strSQL

Duane
Hook'D on Access
MS Access MVP

 

[hondaman2003]

SQLStatement = "UPDATE [Patient table] " & _
"SET [Owner2] = '" & [LAN ID - 1] & "' " & _
"WHERE [Patient table].[Owner2] IN (" & _
"SELECT TOP 2 [Patient Table].Owner2 " & _
"FROM [Patient Table] " & _
"WHERE ([Patient Table].Owner2 Is Null Or [Patient Table].Owner2='') AND [Patient Table].Completed2=0" & _
");"

DoCmd.RunSQL SQLStatement


This generates the same problem.

 

[dhookom]

I didn't expect the message to change. I did expect you to open the debug window and paste the SQL into a blank query to see if you can determine the issue.

If you can't find the error on your own, please post the SQL to this forum for some assistance.

Duane
Hook'D on Access
MS Access MVP

 

[hondaman2003]

When I paste this SQL into a blank query, the SQL code runs correctly. It finds the appropriate number of records and updates them. If I do it in VBA using the docmd.runsql command, it will always find zero records to update.

 

[dhookom]

Again "please post the SQL to this forum for some assistance"

Duane
Hook'D on Access
MS Access MVP

 

[hondaman2003]

I have....twice! Is there something else you need other than this code? To my understanding this is SQL code. I don't mean to be rude if there is more than this code below that you need, please forgive my bluntness.

SQLStatement = "UPDATE [Patient table] " & _
"SET [Owner2] = '" & [LAN ID - 1] & "' " & _
"WHERE [Patient table].[Owner2] IN (" & _
"SELECT TOP 2 [Patient Table].Owner2 " & _
"FROM [Patient Table] " & _
"WHERE ([Patient Table].Owner2 Is Null Or [Patient Table].Owner2='') AND [Patient Table].Completed2=0" & _
");"

DoCmd.RunSQL SQLStatement

 

[dhookom]

That isn't the SQL, it's the code. What I would like to see is the results of:

Debug.Print "strSQL: " & strSQL

or in your case

Debug.Print "SQLStatement: " & SQLStatement

If you don't know how to show the results, just press Ctrl+G.



Duane
Hook'D on Access
MS Access MVP

 

[hondaman2003]

You are right that this is the code. This is VBA code, but the SQL is in the VBA code. SQL is programming code, not results of a query. I'm sorry, that is why I didn't post it after you asked, because I it was already there.

Here are the results of the debug.print

SQLStatement: UPDATE [Patient table] SET [Owner2] = 'b84064' WHERE [Patient table].[Owner2] IN (SELECT TOP 2 [Patient Table].Owner2 FROM [Patient Table] WHERE ([Patient Table].Owner2 Is Null Or [Patient Table].Owner2='') AND [Patient Table].Completed2=0);

 

[dhookom]

So you are suggesting if you simply copy and paste the SQL Statement from the debug window into the SQL view of a query, it will work?

I didn't think a TOP query would work without providing an ORDER BY clause.

Duane
Hook'D on Access
MS Access MVP

 

[hondaman2003]

I think I was assuming to much.

I created a single query that returned the results of the subquery (the top 2 values). Then created another query that simply updated all the results. If that works, why wouldn't this work?

 

[dhookom]

Did you try add an ORDER BY to see if it worked?

Duane
Hook'D on Access
MS Access MVP

 

[hondaman2003]

I added is to the subquery section and it's still not working. It's showing 0 records updated.

Here is the debug

UPDATE [Patient table] SET [Owner2] = 'b84064' WHERE [Patient table].[Owner2] IN (SELECT TOP 2 [Patient Table].Owner2 FROM [Patient Table] WHERE ([Patient Table].Owner2 Is Null Or [Patient Table].Owner2='') AND [Patient Table].Completed2=0 ORDER BY [Owner2]);

 

[dhookom]

I'm not sure why you are ordering by a field that is empty.

Also, the logic seems flawed. Your first WHERE clause that uses IN () is clearly trying to match null values which won't work since the [Owner 2] is blank/null.

I just tried something like this that worked for me:

UPDATE [Patient table] 
SET [Owner2] = 'b84064' 
WHERE [Patient table].[PrimaryKeyField] IN 
(SELECT TOP 2 [Patient Table].[PrimaryKeyField]
 FROM [Patient Table]
 WHERE [Patient Table].Owner2 & '' = ''
 AND [Patient Table].Completed2=0
 ORDER BY [PrimaryKeyField]);

Duane
Hook'D on Access
MS Access MVP

 

[hondaman2003]

That's the ticket!

I didn't have a good understanding of how a subquery worked. You gave me a new perspective. I think I'm going to come to like them.