ACCESS FEED: RE: [MS_AccessPros] Query result as criteria for another query

Selasa, 29 Oktober 2013

RE: [MS_AccessPros] Query result as criteria for another query

Doyce,

I would try

SELECT tblWeeks.YearID, tblWeeks.WeekID, tblCity.CITYInitial, tblCity.CITYNAME, tblWeeks.ID
FROM tblCity, tblWeeks
WHERE tblWeeks.ID>=(SELECT ID FROM qryWeeksStartDate)
ORDER BY tblWeeks.YearID, tblWeeks.WeekID, tblCity.CITYNAME;

I expect you could also use DLookup() to pull the value from qryWeeksStartdate.

Duane Hookom MVP
MS Access

________________________________
> From: winberry.doyce@roadsysinc.com
>
> Pros,
>
> Can a query use another query as criteria for one field? Here is the
> SQL of my query:
>
> SELECT tblWeeks.YearID, tblWeeks.WeekID, tblCity.CITYInitial,
> tblCity.CITYNAME, tblWeeks.ID
> FROM tblCity, tblWeeks
> WHERE (((tblWeeks.ID)>=[qryWeeksStartDate].[ID]))
> ORDER BY tblWeeks.YearID, tblWeeks.WeekID, tblCity.CITYNAME;
>
>
>
> The query prompts me for a value for [qryWeeksStartDate].[ID] which I
> used the query builder to make. I've also tried using
> Queries![qryWeeksStartDate].[ID] but I still get the prompt. When I run
> the query qryWeeksStartDate, I get the result I want. Here is the SQL
> of qryWeeksStartDate:
>
> SELECT tblWeeks.ID
> FROM tblWeeks
> WHERE
> (((tblWeeks.WeekID)=DatePart("ww",[Forms]![frmWeeklyDeliveryReport]![StartDate]))
> AND
> ((tblWeeks.YearID)=DatePart("yyyy",[Forms]![frmWeeklyDeliveryReport]![StartDate])));
>
> How can I get this query to run without the prompt?
>
> Doyce