Passing a criteria as a value from one query to another

[AndrewB]

I have a combo box in a form which allows users to select a Client Group. One of the choices in the combo box is ALL.

I have some code in a STEP1 query that says

SELECT IIf(Forms![Date Picker].[Client Group]="ALL","'SEDP' Or 'LD' Or 'MH'",Forms![Date Picker].[Client Group]) AS Expr1
FROM MPI_PERSON
GROUP BY IIf(Forms![Date Picker].[Client Group]="ALL","'SEDP' Or 'LD' Or 'MH'",Forms![Date Picker].[Client Group]);

I have a STEP2 query to select all records on the MPI_PERSON table and do some slight processing.

Now, in a STEP3 query, I want to select all records from the STEP2 query where the field [Client Group] satisfies the STEP1 criteria. If the Client Group chosen in the Combo box is one of the existing categories i.e. SEDP or MD or LH, it works but where the ALL has been entered, it is returning no records in the final query.

I have tried pasting the results of the STEP1 query into the criteria box as a test - and in that case it does return all records I want. What do I need to do to get the criteria recognised in the QBE box - do I need to use Eval or something?

Here is the SQL for the final query

SELECT [Find all Clients STEP 2].[Paris ID], [Find all Clients STEP 2].DOB, [Find all Clients STEP 2].[DOB Estimated], [Find all Clients STEP 2].Gender, [Find all Clients STEP 2].[Status From Date], [Find all Clients STEP 2].[Status To Date], [Find all Clients STEP 2].STS_MAIN, [Find all Clients STEP 2].Title, [Find all Clients STEP 2].Name, [Find all Clients STEP 2].NAM_FROM, [Find all Clients STEP 2].NAM_TO, [Find all Clients STEP 2].[Client Group], [Find all Clients STEP 1].Expr1, Len([Expr1]) AS Expr2
FROM [Find all Clients STEP 2], [Find all Clients STEP 1]
GROUP BY [Find all Clients STEP 2].[Paris ID], [Find all Clients STEP 2].DOB, [Find all Clients STEP 2].[DOB Estimated], [Find all Clients STEP 2].Gender, [Find all Clients STEP 2].[Status From Date], [Find all Clients STEP 2].[Status To Date], [Find all Clients STEP 2].STS_MAIN, [Find all Clients STEP 2].Title, [Find all Clients STEP 2].Name, [Find all Clients STEP 2].NAM_FROM, [Find all Clients STEP 2].NAM_TO, [Find all Clients STEP 2].[Client Group], [Find all Clients STEP 1].Expr1, Len([Expr1])
HAVING ((([Find all Clients STEP 2].[Client Group])=[Find all Clients STEP 1].[Expr1]));

Hope there isn't too much "Social Services speak" in there - I can also strip down non-crucial fields if it makes it easier to follow what I'm on about.

 

[AndrewB]

Anyone able to help please?

 

[Disturbedguy]

Andrew B ive been waiting for a reply for a nearly a week m8, looks as if they dont care about new members.

 

[Smart]

REPassing a criteria as a value from one query to another

Write a new query that references both your original queries (dont forget to link them)
Then select the fields you want

 

[AndrewB]

Write a new query that references both your original queries (dont forget to link them)
Then select the fields you want

My STEP3 query is something that references both - but it doesn't work whether I link them or don't link them and whether the join is any of the available options.