MS Access query requires a Parameter to be enter by the user if I have combined custom columns and have a criteria in that column

In the following query everything works well

SELECT gpname.group_name1, physician.group_number, physician.member_number, physician.phys_last_name, physician.phys_first_name, [Book Purchase Physicians - 2019].copies, [Book Purchase Physicians - 2019].invoice_total, [Book Purchase Physicians - 2020].copies, [Book Purchase Physicians - 2020].invoice_total, [Book Purchase Physicians - 2021].copies, [Book Purchase Physicians - 2021].invoice_total, IIf([Book Purchase Physicians - 2019]![copies]>0,[Book Purchase Physicians - 2019]![copies],0) AS copy1, IIf([Book Purchase Physicians - 2020]![copies]>0,[Book Purchase Physicians - 2020]![copies],0) AS copy2, IIf([Book Purchase Physicians - 2021]![copies]>0,[Book Purchase Physicians - 2021]![copies],0) AS copy3, [copy1]+[copy2]+[copy3] AS gtotal
FROM (((physician LEFT JOIN [Book Purchase Physicians - 2019] ON physician.member_number = [Book Purchase Physicians - 2019].member_number) LEFT JOIN [Book Purchase Physicians - 2020] ON physician.member_number = [Book Purchase Physicians - 2020].member_number) LEFT JOIN [Book Purchase Physicians - 2021] ON physician.member_number = [Book Purchase Physicians - 2021].member_number) LEFT JOIN gpname ON physician.group_number = gpname.group_number
WHERE (((physician.active_phys)="Y" Or (physician.active_phys)="G"));

But if I want to add a criteria to column gtotal

SELECT gpname.group_name1, physician.group_number, physician.member_number, physician.phys_last_name, physician.phys_first_name, [Book Purchase Physicians - 2019].copies, [Book Purchase Physicians - 2019].invoice_total, [Book Purchase Physicians - 2020].copies, [Book Purchase Physicians - 2020].invoice_total, [Book Purchase Physicians - 2021].copies, [Book Purchase Physicians - 2021].invoice_total, IIf([Book Purchase Physicians - 2019]![copies]>0,[Book Purchase Physicians - 2019]![copies],0) AS copy1, IIf([Book Purchase Physicians - 2020]![copies]>0,[Book Purchase Physicians - 2020]![copies],0) AS copy2, IIf([Book Purchase Physicians - 2021]![copies]>0,[Book Purchase Physicians - 2021]![copies],0) AS copy3, [copy1]+[copy2]+[copy3] AS gtotal
FROM (((physician LEFT JOIN [Book Purchase Physicians - 2019] ON physician.member_number = [Book Purchase Physicians - 2019].member_number) LEFT JOIN [Book Purchase Physicians - 2020] ON physician.member_number = [Book Purchase Physicians - 2020].member_number) LEFT JOIN [Book Purchase Physicians - 2021] ON physician.member_number = [Book Purchase Physicians - 2021].member_number) LEFT JOIN gpname ON physician.group_number = gpname.group_number
WHERE (((physician.active_phys)="Y" Or (physician.active_phys)="G") AND (([copy1]+[copy2]+[copy3])>0));

Access will ask for parameter values for copy1, copy2, and copy3. Why does the search criteria trigger the parameter requests? I get no request without the criteria and total shows the expected values.

Answer

Prompts because copy1, copy2, copy3 are not fields in data source. They are calculated in query. Fields copy1, copy2, copy3 don’t actually exist for purposes of filtering. SQL is okay with doing calculations with calculated fields (your gtotal calc) but WHERE clause expressions must use actual fields from data source. Options:

repeat the calculation for each in the WHERE clause: calc1here + calc2here + calc3here > 0
build another query that uses working query as data source
build a report using working query as RecordSource and apply gtotal filter to report when opening

Advertisement

Answer