CODEWITHSUNDEEP
ms-accesssql-update
unseen_rider
unseen_rider

Reputation: 324

MS Access update query asks for parameter value - query depending on select query

The UPDATE query below interprets qry_Breakd_DYFYHAS_CountRecsDateRange.CountRecords_InDateRange to be a parameter and asks for a parameter value.

The qry_Breakd_DYFYHAS_CountRecsDateRange query works, and gives the number: 8.

Contents of Update query:

    UPDATE tbl_total_listenings_calcs 
    SET tbl_total_listenings_calcs.Total_listenings_tbl_Data_DateRange = qry_Breakd_DYFYHAS_CountRecsDateRange.CountRecords_InDateRange;

Contents of "qry_Breakd_DYFYHAS_CountRecsDateRange" query:

    SELECT count(*) AS CountRecords_InDateRange
    FROM tbl_Data, tbl_DateFromTo
    WHERE (((tbl_Data.Date_Listening) Between tbl_DateFromTo.Date_From And tbl_DateFromTo.Date_To));

Seeking advice and suggestions on what to change in the two queries as applicable to get the update query working.

Upvotes: 1

Views: 188

Answers (1)

HansUp
HansUp

Reputation: 97131

That UPDATE references only tbl_total_listenings_calcs, and it is not joined to any other table or query. So when Access sees qry_Breakd_DYFYHAS_CountRecsDateRange.CountRecords_InDateRange, all it knows is that is not one of the query's data sources, so assumes it must be a parameter.

Use DLookup() to fetch the CountRecords_InDateRange value from the qry_Breakd_DYFYHAS_CountRecsDateRange query.

UPDATE tbl_total_listenings_calcs 
SET Total_listenings_tbl_Data_DateRange = 
    DLookup
        (
            "CountRecords_InDateRange",
            "qry_Breakd_DYFYHAS_CountRecsDateRange"
        );

You could check whether it works with a subquery instead of DLookup() ...

UPDATE tbl_total_listenings_calcs 
SET Total_listenings_tbl_Data_DateRange = 
    (
        SELECT CountRecords_InDateRange
        FROM qry_Breakd_DYFYHAS_CountRecsDateRange
    );

However, sometimes Access treats an UPDATE with a subquery as "not updateable". I'm not certain what would happen in this case. But DLookup() will surely work.

Upvotes: 1

Related Questions