Hello,

I am trying to build an UPDATE query in Access 2003 via the Design view but keep getting an 'Enter Parameter Value' dialogue every time I attempt to preview the results in datasheet view.

The update query is referencing the results of a saved union query that normalises data imported into a temporary table from a .csv file
I am using the same Union query as the source to an append query which works fine but it doesn't want to know for the update

The SQL is

UPDATE tblCommodityResource SET tblCommodityResource.Allocation = [qryUnionResourceColumnsToRows]![Allocation]
WHERE (((tblCommodityResource.ShiftID)=[qryUnionResourceColumnsToRows]![ShiftID]) AND ((tblCommodityResource.Commodity)=[qryUnionResourceColumnsToRows]![Commodity]));

Any ideas why this isn't working or is it just bad syntax?

Many thanks.

What parameter it is asking for
Also check your field names are they written properly

It's asking for the values from both field names of the union query, qryUnionResourcesColumnsToRows, specified in the WHERE clause.

These fields were added as Criteria using the Expression builder so I think they're spelt correctly.

Is qryUnionResourcesColumnsToRows part of your current query

thanks for the assistance :)

No, qryUnionResourcesColumnsToRows is union type query that converts a flat-file import table into a more normalised looking dataset. The dataset has three fields ShiftID, Commodity and Allocation.

The SQL for the union is:

SELECT ShiftID, "Commodity1" AS Commodity, Resource1 AS Allocation
FROM tblImportShift
WHERE Resource1 Is Not Null
UNION ALL
SELECT ShiftID, "Commodity2", Resource2 .....

In addition to an autonumber PK, the table to be updated, tblCommodityResource has the same three fields.

The aim is to only update rows in tblCommodityResource where the ShiftID and Commodity match those in the Union query.

Is this possible?

You cannot use another query field as criterea unless that query is a part of current query i-e joined or included in the grid atleased