473,287 Members | 1,395 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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;
  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
6 2388
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
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
Denburt
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).

Me!FiltCost
Mar 15 '07 #5
Denburt
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

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

Similar topics

7
by: Kornelius Finkenbein | last post by:
Hello folks! I've got a strange problem with my download-script in conjunction with M$ internet explorer, if the filename I want to link to includes more than one points. In Netscape the problem...
2
by: CES | last post by:
All, I'm at a loss, the code below works in IE but not in Netscape and I'm unskilled enough not to know why. Essentially this code looks thru all of the form fields and if the input box has a...
43
by: JohnB | last post by:
i've noticed some sites have a custom icon in the address bar, next to the url. how can i do that? thanks. john b.
0
by: Alvey Sidecast | last post by:
Well it is to me anyway. ------------------------------ Access Version = 2002 OS = XP Pro User = Ordinary to bumbling ------------------------------ I was writing some sql yesterday which...
7
by: Dan H. | last post by:
Hello, What is the equivalent "string resource file" in c#? And, is there a way to create a beep in C#? Thanks, Dan
14
by: I_AM_DON_AND_YOU? | last post by:
Hello all: Yesterday I put this post regarding the query as to how to open a new form in "modal" style. I am calling the form in the following way: dim frm as form2() frm = new form2()...
12
by: Sean | last post by:
I know I can pass a reference to a String into a Form by overloading the Constructor. Public Sub New(ByRef strDX As String) MyBase.New() InitializeComponent() End Sub
6
by: Vincent Finn | last post by:
Hi, I am trying to store an object in the Session() I have a VB.Net control which is loaded in an asp page and then calls a function in my webservice (also written in VB.Net) The web...
4
by: Påhl Melin | last post by:
I have some problems using conversion operators in C++/CLI. In my project I have two ref class:es Signal and SignalMask and I have an conversion function in Signal to convert Signal:s to...
10
by: morangolds | last post by:
Hi, I've been having a problem with C++ Windows Forms apps not "ending" when you close the form window. I've searched about this problem all over the place and most searches have lead me to...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.