How to define CRITERIA in ACCESS QUERY to be taken from a field in another table?

Question

I have created a simple query with filtering criteria for date, which I typed manually in design form of the query.

    SELECT tbl_CountryDefs.Country_Name, tbl_CRatings.CReportDate, tbl_CRatings.CRating
    FROM tbl_CRatings INNER JOIN tbl_CountryDefs ON tbl_CRatings.CCountry = tbl_CountryDefs.CMAPPING
    WHERE (((tbl_CRatings.CReportDate)="2013-JAN"))
    ORDER BY tbl_CountryDefs.Country_Name;

If I create a new table with a criteria definition as field, how can I without any macros or VBA to define criteria in that query to look up for the value of the field in the new table?

Answer 1

Unless I've misunderstood you can achieve what you're looking for with a simple DLookup. If you create a new table (say called QueryCriteria, with one Text field called ReportDate), then the following should work:

SELECT tbl_CountryDefs.Country_Name, tbl_CRatings.CReportDate, tbl_CRatings.CRating
FROM tbl_CRatings INNER JOIN tbl_CountryDefs ON tbl_CRatings.CCountry = tbl_CountryDefs.CMAPPING
WHERE tbl_CRatings.CReportDate = DLookup("ReportDate","QueryCriteria")
ORDER BY tbl_CountryDefs.Country_Name;

In this example, if the ReportDate in the QueryCriteria table had a value of 2013-JAN, it would achieve the same as your original hard-coded query.