Nested sql on search form - "in" works, "not in" doesn't

[KiwiGenie]

Hi..I am trying to make a search form. I am fairly new to access and could well be looking at it completely wrong. I have an unbound form with textboxes in the header for entering different search criteria.
I have a subform for displaying the results, which is bound to Query4. SQL for Query4 (taken from sql view in query):

SELECT tblRecipes.RecipeName, tblRecipes.FoodCategory, Sum(Query3.IngredCost) AS SumOfIngredCost, Query3.RecipeID
FROM Query3 INNER JOIN tblRecipes ON Query3.RecipeID = tblRecipes.RecipeID
GROUP BY tblRecipes.RecipeName, tblRecipes.FoodCategory, Query3.RecipeID;
 

(Query3 is the recordsource for my main recipe form’s subform, and performs the calculations on the ingredients included in each recipe, then Query4 groups each recipe’s ingredients together using RecipeName field, and sums each recipe’s cost so I can search by cost)
So on my search form I have a command button which builds and applies a filter to the subform according to what is in the textboxes.
Its all working how I wanted it to, I can search by part of a name, by category, by cost or by ingredient perfectly. But when I try to turn the ingredient search around to find recipes that do not include x ingredient ( by replacing “in” with “not in”) I get an error – Run-time error 2001: You canceled the previous operation. Here is my code:

Private Sub CommandSearch_Click()
Dim strWhere As String
Dim lngLen As Long
 
If Not IsNull(Me.FiltRecName) Then
strWhere = strWhere & "([RecipeName] like ""*" & Me.FiltRecName & "*"") And "
End If
 
If Not IsNull(Me.FiltCat) Then
strWhere = strWhere & "([FoodCategory] = """ & Me.FiltCat & """) And "
End If
 
If Not IsNull(Me.FiltCost) Then
strWhere = strWhere & "([SumOfIngredCost] < " & Me.FiltCost & ") And "
End If
 
If Not IsNull(Me.FiltIng1) Then
strWhere = strWhere & "([RecipeID]in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = """ & Me.FiltIng1 & """)) And "
End If
 
If Not IsNull(Me.FiltIng2) Then
strWhere = strWhere & "([RecipeID] not in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = """ & Me.FiltIng2 & """)) And "
End If
 
'Apply the filter
lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen <= 0 Then
MsgBox "No criteria."
Else
‘’’’’’’‘On pressing debug, this line is highlighted:
Me.subfrmResults.Form.Filter = Left(strWhere, lngLen)
Me.subfrmResults.Form.FilterOn = True
End If
End Sub
 

Can anyone help me with this, or suggest a better way? I’m using Access97.

[KiwiGenie]

I'm still struggling with this - I can't even get "Not In" to work in a straight query:
This query works perfectly:

SELECT Query4.RecipeName, Query4.FoodCategory, Query4.SumOfIngredCost, Query4.RecipeID
FROM Query4
WHERE (((Query4.RecipeID) In (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = [Me].[FiltIng1])));

When I replace "In" with "Not In" I get an error - "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.":

SELECT Query4.RecipeName, Query4.FoodCategory, Query4.SumOfIngredCost, Query4.RecipeID
FROM Query4
WHERE (((Query4.RecipeID) Not In (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = [Me].[FiltIng1])));

Please can someone help me figure this out?

[Denburt]

Try this should work.

SELECT Query4.RecipeName, Query4.FoodCategory, Query4.SumOfIngredCost, Query4.RecipeID
FROM Query4
WHERE Not Query4.RecipeID In (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = [Me].[FiltIng1]);

[KiwiGenie]

Hi Denburt..thanks for your suggestion, I tried your modified code, unfortunately I still get the same error. I'm sure this must be possible somehow, its really driving me mad!

[Denburt]

This one is interesting,

If the following statement works for you...

If Not IsNull(Me.FiltIng1) Then
strWhere = strWhere & "([RecipeID]in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE [Ingredient] = """ & Me!FiltIng1 & """)) And "
End If

Then I dont see why the following wouldn't work unless there is a problem elsewhere then again I could be overlooking something real simple (I do that at the end of a long day sometimes:

If Not IsNull(Me.FiltIng2) Then
strWhere = strWhere & "( not [RecipeID] in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE [Ingredient] = """ & Me!FiltIng2 & """)) And "
End If

As a tip:
When you are refering to properties use the period after Me.
When you are refering to objects such as a textbox control use the exclamation (could be as simple as that sometimes).

Me!FiltCost

[Denburt]

OK I reread your post and looking at what you are trying to accomplish I would think you could simply use the following and it would achieve your results.

If Not IsNull(Me.FiltIng2) Then
strWhere = strWhere & "([RecipeID] in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] <> """ & Me.FiltIng2 & """)) And "
End If

[KiwiGenie]

Sorry its taken me so long to get back to this - I couldn't get back to this site on Friday.
I'm still not clear on why this wouldn't work, surely if something works one way it will work the opposite too. Anyway I have got it pulling the right records now, by changing the query that the (results) subform is based on. I couldn't do your last suggestion because of the one-to-many relationship between a recipe and its ingredients - eg if searching for recipes that do not use butter, all recipes include at least one ingredient that is not butter, so all records are returned.
So the new query is:

SELECT tblRecipes.RecipeID, tblRecipes.RecipeName, tblRecipes.FoodCategory, tblRecipeIngredients.Ingredient, Format(DSum("[Query3]![IngredCost]","[Query3]","[Query3]![RecipeID] =" & [tblRecipes]![RecipeID]),"Currency") AS SumOfIngredCost
FROM tblRecipes INNER JOIN tblRecipeIngredients ON tblRecipes.RecipeID = tblRecipeIngredients.RecipeID;

Then instead of

Me.subfrmResults.Form.Filter = Left(strWhere, lngLen)
Me.subfrmResults.Form.FilterOn = True

I now have

DoCmd.OpenForm "frmWait"
Me.subfrmResults.Form.RecordSource = "SELECT DISTINCT Query2.RecipeID, Query2.RecipeName, Query2.FoodCategory, Query2.SumOfIngredCost FROM Query2 WHERE " & (Left(strWhere, lngLen))
DoCmd.Close acForm, "frmWait"

This works, but is slow, which is why I put in the command to open the form frmWait which tells the user to please wait.
Thanks very much for trying to help, this one sure strained the brain! I think even when you don't get the answer from a forum like this, it still helps anyway to look at things differently, or even just to see your problem laid out in plain text.