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

Question

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.

HansUp · Accepted Answer · 2016-03-04 14:03:11Z

1

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.

edited Mar 4, 2016 at 14:03

answered Mar 3, 2016 at 17:49

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

unseen_rider Over a year ago

Ok I'll try those and let you have feedback soon.

unseen_rider Over a year ago

You are missing a comma, between the fieldname and the query name in the Dlookup. Then the Dlookup works. Thanks

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related