By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 763 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

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

P: 41
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):

Expand|Select|Wrap|Line Numbers
  1. SELECT tblRecipes.RecipeName, tblRecipes.FoodCategory, Sum(Query3.IngredCost) AS SumOfIngredCost, Query3.RecipeID
  2. FROM Query3 INNER JOIN tblRecipes ON Query3.RecipeID = tblRecipes.RecipeID
  3. GROUP BY tblRecipes.RecipeName, tblRecipes.FoodCategory, Query3.RecipeID;
  4.  
(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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandSearch_Click()
  2. Dim strWhere As String
  3. Dim lngLen As Long
  4.  
  5. If Not IsNull(Me.FiltRecName) Then
  6. strWhere = strWhere & "([RecipeName] like ""*" & Me.FiltRecName & "*"") And "
  7. End If
  8.  
  9. If Not IsNull(Me.FiltCat) Then
  10. strWhere = strWhere & "([FoodCategory] = """ & Me.FiltCat & """) And "
  11. End If
  12.  
  13. If Not IsNull(Me.FiltCost) Then
  14. strWhere = strWhere & "([SumOfIngredCost] < " & Me.FiltCost & ") And "
  15. End If
  16.  
  17. If Not IsNull(Me.FiltIng1) Then
  18. strWhere = strWhere & "([RecipeID]in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = """ & Me.FiltIng1 & """)) And "
  19. End If
  20.  
  21. If Not IsNull(Me.FiltIng2) Then
  22. strWhere = strWhere & "([RecipeID] not in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = """ & Me.FiltIng2 & """)) And "
  23. End If
  24.  
  25. 'Apply the filter
  26. lngLen = Len(strWhere) - 5 'Without trailing " AND "
  27. If lngLen <= 0 Then
  28. MsgBox "No criteria."
  29. Else
  30. ‘’’’’’’‘On pressing debug, this line is highlighted:
  31. Me.subfrmResults.Form.Filter = Left(strWhere, lngLen)
  32. Me.subfrmResults.Form.FilterOn = True
  33. End If
  34. End Sub
  35.  
Can anyone help me with this, or suggest a better way? I’m using Access97.
Mar 12 '07 #1
Share this Question
Share on Google+
6 Replies


P: 41
I'm still struggling with this - I can't even get "Not In" to work in a straight query:
This query works perfectly:
Expand|Select|Wrap|Line Numbers
  1. SELECT Query4.RecipeName, Query4.FoodCategory, Query4.SumOfIngredCost, Query4.RecipeID
  2. FROM Query4
  3. 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.":
Expand|Select|Wrap|Line Numbers
  1. SELECT Query4.RecipeName, Query4.FoodCategory, Query4.SumOfIngredCost, Query4.RecipeID
  2. FROM Query4
  3. WHERE (((Query4.RecipeID) Not In (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = [Me].[FiltIng1])));
Please can someone help me figure this out?
Mar 15 '07 #2

Denburt
Expert 100+
P: 1,356
Try this should work.
Expand|Select|Wrap|Line Numbers
  1. SELECT Query4.RecipeName, Query4.FoodCategory, Query4.SumOfIngredCost, Query4.RecipeID
  2. FROM Query4
  3. WHERE Not Query4.RecipeID In (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = [Me].[FiltIng1]);
Mar 15 '07 #3

P: 41
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!
Mar 15 '07 #4

Denburt
Expert 100+
P: 1,356
This one is interesting,

If the following statement works for you...
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.FiltIng1) Then
  2. strWhere = strWhere & "([RecipeID]in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE [Ingredient] = """ & Me!FiltIng1 & """)) And "
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.FiltIng2) Then
  2. strWhere = strWhere & "( not [RecipeID] in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE [Ingredient] = """ & Me!FiltIng2 & """)) And "
  3. 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
Mar 15 '07 #5

Denburt
Expert 100+
P: 1,356
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.
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.FiltIng2) Then
  2. strWhere = strWhere & "([RecipeID] in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] <> """ & Me.FiltIng2 & """)) And "
  3. End If
Mar 15 '07 #6

P: 41
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblRecipes.RecipeID, tblRecipes.RecipeName, tblRecipes.FoodCategory, tblRecipeIngredients.Ingredient, Format(DSum("[Query3]![IngredCost]","[Query3]","[Query3]![RecipeID] =" & [tblRecipes]![RecipeID]),"Currency") AS SumOfIngredCost
  2. FROM tblRecipes INNER JOIN tblRecipeIngredients ON tblRecipes.RecipeID = tblRecipeIngredients.RecipeID;
Then instead of
Expand|Select|Wrap|Line Numbers
  1. Me.subfrmResults.Form.Filter = Left(strWhere, lngLen)
  2. Me.subfrmResults.Form.FilterOn = True
I now have
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmWait"
  2. Me.subfrmResults.Form.RecordSource = "SELECT DISTINCT Query2.RecipeID, Query2.RecipeName, Query2.FoodCategory, Query2.SumOfIngredCost FROM Query2 WHERE " & (Left(strWhere, lngLen))
  3. 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.
Mar 19 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.