Using MS Access/Access subquery
QUESTION: Dear Scott
I am attempting to write an IIF statement using a subquery as the criteria for the query.
I have a table of 15 names the SQL of which is (SELECT [Pilot Lodges].Lodge
FROM [Pilot Lodges];)
I have tried using it as the criteria: IIf([Forms]![StatsByYear]![Check258]=-1,(SELECT [Pilot Lodges].Lodge
FROM [Pilot Lodges];),””)
It shows an error that it can only produce one record. It does not actually produce anything. I have tried using EXISTS in front of the SQL but it does not work.
I have tried another method: IIf([Forms]![StatsByYear]![Check258]=-1,”A Lodge”,””) This works but if I add: OR “A Lodge” it produces and error.
Please advise where I am going wrong and the best way of doing this.
ANSWER: If your table is only a single field with the Lodge names, then referencing a checkbox on a form will not do anything for you. If you had a Yes/No field in the table, you could use the checkbox as a filter.
I really don't understand what you are trying to do. It appears you want to select some of the lodges, but have provided no rules as to how you will select them.
---------- FOLLOW-UP ----------
QUESTION: Dear Scott......I fear I did not explain myself particularly well.
My data base has a large number of members within 85 Lodges and out of it falls a large amount of statistical data which can be altered by changing the day, month or year, From and To, on a statistical form, so that I can look at any statistical data from any point. This works well. 15 of those Lodges have been selected to take part in a pilot project between two pilot dates. When the check box is ticked it changes the dates to the dates of the pilot and the statistics reflect that change but reflects all the 85 Lodges rather than the 15 pilot Lodges.
I have therefore produced a table listing those 15 pilot Lodges and when I tick the check box I want all those pilot Lodges to be the criteria of the Lodge field of the main query, so it shows only the statics of those pilot Lodges between the pilot dates.
The IIF statement within the Lodge field criteria is to recognise when the check box is ticked,(IIF([Forms]![StatsByYear]![Check258]=-1), that the pilot Lodges are the True part,(IN(SELECT [Pilot Lodges].Lodge FROM [Pilot Lodges])and (,"")-(All Lodges)is the False.
I am no programmer but I thought the best way was achieve this was to nest the SQL of the 15 Lodges within the IIF statement as the true part. I have since tried using IIF([Forms]![StatsByYear]![Check258]=-1,IN(SELECT [Pilot Lodges].Lodge FROM [Pilot Lodges];),””)but this also does not work.
I am looking to see whether I am doing this the correct way and if so, where I am going wrong, or finding out the best way of achieving it.
If I type all 15 Lodges as the criteria using OR then of course that works but I want it to do that automatically by putting a tick in a check box.
I apologise for my earlier poor explanation and hope this is a little better.
I understand better. You were close in the idea of using a subquery, but you can't do it conditionally like that.
You have 2 options. Either use separate saved queries or generate a SQL statement in VBA code. Since you have a table of the pilot lodges, you don't need a subquery. Just add the pilot table, joined on LodgeID.
The way I would handle this is to modify the Recordsource of the form and/or report you want to produce at runtime based on the checkbox.