469,342 Members | 5,948 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

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

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;
(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
  5. If Not IsNull(Me.FiltRecName) Then
  6. strWhere = strWhere & "([RecipeName] like ""*" & Me.FiltRecName & "*"") And "
  7. End If
  9. If Not IsNull(Me.FiltCat) Then
  10. strWhere = strWhere & "([FoodCategory] = """ & Me.FiltCat & """) And "
  11. End If
  13. If Not IsNull(Me.FiltCost) Then
  14. strWhere = strWhere & "([SumOfIngredCost] < " & Me.FiltCost & ") And "
  15. End If
  17. If Not IsNull(Me.FiltIng1) Then
  18. strWhere = strWhere & "([RecipeID]in (SELECT [RecipeID] FROM tblRecipeIngredients WHERE[Ingredient] = """ & Me.FiltIng1 & """)) And "
  19. End If
  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
  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
Can anyone help me with this, or suggest a better way? I’m using Access97.
Mar 12 '07 #1
6 2124
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
1,356 Expert 1GB
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
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
1,356 Expert 1GB
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).

Mar 15 '07 #5
1,356 Expert 1GB
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
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.

Similar topics

7 posts views Thread by Kornelius Finkenbein | last post: by
43 posts views Thread by JohnB | last post: by
reply views Thread by Alvey Sidecast | last post: by
7 posts views Thread by Dan H. | last post: by
14 posts views Thread by I_AM_DON_AND_YOU? | last post: by
12 posts views Thread by Sean | last post: by
6 posts views Thread by Vincent Finn | last post: by
4 posts views Thread by Påhl Melin | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.