CODEWITHSUNDEEP
ms-access
Toms Krišāns
Toms Krišāns

Reputation: 1

Update query does not return any values

When I try to create a simple update query with Subtraction or anything else in the same table, it doesn't return me any value, could it be something to do with formating?

This is SQL code: UPDATE Sales SET Sales.GrossProfit = [SubTot]-[Cost];

It doesn't return any value.

I have copied syntax from different databases (from colleagues) where the same type of query worked. Could it be something to do with my general Access settings?

UPDATE Sales SET Sales.GrossProfit = [SubTot]-[Cost];

I expect the query to update the GrossProfit column with the calculation "SubTot - Cost"

Upvotes: 0

Views: 2041

Answers (1)

C Perkins
C Perkins

Reputation: 3884

Strictly speaking, an UPDATE query does not "return" any columns. It only changes data in existing rows, but by itself does not return those changes back to you. (It is more accurately called an Update statement since it does not return data.)

For instance, if you were to execute the UPDATE statement in VBA code, you would not get a RecordSet of columns like with a SELECT query. To get the results after executing the UPDATE statement would require that you execute a separate SELECT query.

However, I assume that you are viewing the Update query in the Access query designer. Correct?

In that case, when you click on the View button (Datasheet View), Access tries to be smart & useful about the UPDATE and converts it temporarily into a SELECT query so that you can visualize the existing data in the columns. The Datasheet View does not actually run the UPDATE statement, nor does it show a preview of what will happen when you do run it.

While in Design View or SQL View for an Update query (or for Delete, Make Table, and Append queries), there is a Run button with a red exclamation point (on the Design ribbon/toolbar). Clicking that button will actually execute the query that changes the table values. Although it will not show you results row by row, you should get a pop-up telling you how many rows were changed due to the statement. (You may also get confirmation prompts that the statement is about to change data in the table.)

Run button for Access Update query

Upvotes: 1

Related Questions