I have a subquery SQL in VBA that translates to this:
SELECT * FROM [R_MemberReporting]
WHERE MemberID In(SELECT MemberID FROM [R_ActivityReporting] WHERE ActivityDate Between #22 Sep 2008# And #22 Oct 2008# )
R_MemberReporting and R_ActivityReporting are both queries that return similar results, but the activity one has many records for a member. My mission is to filter the R_ActivityReporting, then get only those records from R_MemberReporting that are result.
Sounded simple to me.
I can even get that SQL, stick it in a query and run it perfectly.
But when I try to open a report using this exact same SQL, I get this message and I don't understand what I am missing:
3306 - You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's from clause. Revise the SELECT statement of the subquery to request only one field.
Now that sounds logical, but the subquery
SELECT MemberID FROM [R_ActivityReporting] WHERE ActivityDate Between #22 Sep 2008# And #22 Oct 2008#
Does only return one field.
I tried first joining the two queries together with a link and showing only DISTINCT fields from R_MemberReporting, but that didn't work because I need to apply a filter to the R_ActivityReporting first ... as you can see in the subquery.
Any ideas what I'm missing?
Here's the VBA that generates the SQL (based on report dialog settings):
vSQL = "SELECT * FROM [R_MemberReporting] WHERE MemberID In(SELECT MemberID FROM [R_ActivityReporting] WHERE " & vMainCriteria & " )"