Subquery syntax

[Les Isaacs]

Hi All

I have only recently started to tackle subqueries, and am now stuck on this syntax:

InThisPayrun: Exists (SELECT staffs.practice, stafpay.[month name], stafpay.staff_name
FROM staffs INNER JOIN stafpay ON staffs.staff_name = stafpay.staff_name
WHERE (((staffs.practice)= [Forms]![frm x main]![prac name]) AND ((stafpay.[month name])= [Forms]![frm x main]![month name] )))

When I try to run the main query I just get a syntax error message on this field.

I obtained the sql for the Select statement by creating the query in design view, testing that the query ran OK, then switching to sql view and copying the code, then adding

InThisPayrun: Exists ()

to my main query, then pasting the Select statement into the brackets.

I used the same syntax with the Exists operator in another subquery and all was well, and I can't see the difference in the syntax I'm using here.:banghead:

If anyone has any ideas I'd be extremely grateful.
Many thanks
Les

 

[apr pillai]

You are using the Sub-Query in the Main Query Column, right?

The EXISTS clause evalutes to True or False and returns only a single output value. In this case several fields are specified in the SELECT clause.

InThisPayrun: Exists(SELECT staffs.practice, stafpay.[month name], stafpay.staff_name FROM staffs....

If you need a single column value to be retrieved for the Main Query Column then use the following syntax:

InThisPayrun: (SELECT staffs.practice FROM staffs .......

Remember, you are trying to retrieve value for a single Column and it cannot hold more than one field value.

Check the following link for more details:

Sub-query in Query Column Expressions

 

[Les Isaacs]

Hello apr pillai

Many thanks for your reply. I didn't realise that you could only have one field in the sub query, so thanks for pointing that out. This causes another problem for me though, as I needed to set criteria on TWO fields from the sub query! Can I do that?

Thanks again
Les