Help Please - Set Query Criteria to Another Query

[graviz]

Here is what I have:



1. A form with a combox based upon a table called "Month_Lookup"
Month_Lookup: -Month (This is the value in the form)
-Start Date
-End Date

2. A query called "Month_Table": -Month (Criteria is from combobox: [Forms]![Main]![cbo_Month]
-Start_Date
-End_Date
-New_SD: DateAdd("m",-1,[Start_Date])
-New_ED: DateAdd("m",-1,[End_Date])

3. Many different queries I would like to base the date criteria off the one above. Here is the code I tried but it didn't work. Not sure how to do this.
>=[Query]![Month_Table]![New_SD] And <=[Query]![Month_Table]![New_ED]

What am I doing wrong and how can I accomplish this?

[pbaldy]

If that query will only return one record, you can include it in the FROM clause of the second query, and your criteria would be:

Between [Month_Table]![New_SD] And [Month_Table]![New_ED]

[graviz]

If that query will only return one record, you can include it in the FROM clause of the second query, and your criteria would be:

Between [Month_Table]![New_SD] And [Month_Table]![New_ED]

I tired your code and it gives me the "Enter Parameter Vaue" error. I also verified the "Month_Table" query only produces 1 line. Any other ideas?

[pbaldy]

Is that query included in the FROM clause? What's the full SQL of this query now?

[graviz]

Is that query included in the FROM clause? What's the full SQL of this query now?

Here's the SQL from the first query "Month_Table":

SELECT Month_Lookup.Month, Month_Lookup.Start_Date, Month_Lookup.End_Date, DateAdd("m",-1,[Start_Date]) AS New_SD, DateAdd("m",-1,[End_Date]) AS New_ED
FROM Month_Lookup
WHERE (((Month_Lookup.Month)=[Forms]![Main]![cbo_Month]));

Here's the SQL from one of the second queries:
SELECT dbo_tbl_summary_30day_detailed.completed, dbo_tbl_summary_30day_detailed.opportunity, dbo_tbl_summary_30day_detailed.facility_number INTO RAW_30Day
FROM dbo_tbl_summary_30day_detailed
WHERE (((dbo_tbl_summary_30day_detailed.account_create_d ate)>=[Month_Table]![New_SD] And (dbo_tbl_summary_30day_detailed.account_create_dat e)<=[Month_Table]![New_ED]));

[pbaldy]

Like I said, you have to include the query in the FROM clause:

FROM dbo_tbl_summary_30day_detailed, Month_Table

Normally adding a second table/query to the FROM clause without a join would result in a Cartesian product, which is why I confirmed that the query would only return one record.

[graviz]

Like I said, you have to include the query in the FROM clause:

FROM dbo_tbl_summary_30day_detailed, Month_Table

Normally adding a second table/query to the FROM clause without a join would result in a Cartesian product, which is why I confirmed that the query would only return one record.

It worked. Sorry I don't really know SQL so I didn't catch that. I didn't think about adding the table as I know you typically don't want to do that to your point. Thanks!

[pbaldy]

No problem, glad we got it sorted out.