How do I workaround query being too complex

[wordbrew]

I thought I was done with my database, everything was working great, but after going into the query that my subform is based on and simply changing a field from ascending to descending, then closing that and trying to open my form, I got the "query is too complex" error and then everthing went south. Access closed itself and the file was unrecoverable. Luckily I had saved this file with 2 other backups! I tested again. Everything works fine in the form if I don't touch anything else, but just by opening and closing the query (without trying to change anything), the same "too complex scenario" happens again.

The query has 13 fields, with 8 of them having had criteria similar to this typed in them:

[Forms]![frmRevisionAudit]![cboRevisionAudit] Or [Forms]![frmRevisionAudit]![cboRevisionAudit] Is Null.

One of the fields had the criteria [Forms]![frmRevisionAudit]![cboYear] Or [Forms]![frmRevisionAudit]![cboYear] = "All".

Once I close this, if I open it back up, the query has restructured itself by filling the rows with every possible combination of criteria to return my results to the main form based on 7 combo box filters.

Is there a way for me to word this or structure this differently or use different phrasing to get my desired results without bloating my query sql a mile long?

I really appreciate any help or suggestions. I'm still an amateur at Access and am fully willing to be called a dunderhead if this is a common mistake to avoid. :) I only care about learning, improving, and getting to the final working end result.

Thanks so much!

[TheSmileyCoder]

Try posting your full SQL syntax here. Are you working only internally in Access, or does the access database run of a backend (and if so, what kind?)

[wordbrew]

Thanks for replying Smiley!

All of this is done in Access alone. Unfortunately, what seems simple when entering into the criteria section of the query design view, bloats into a huge sql statement. I'm going to only post a snippet (which is still big) because posting the entire statement would too cumbersome. Hopefully this will give you the idea.

SELECT tblRevisionAudit.RevisionNumberLookup,
       tblRevisionAudit.BagNumberLookup,
       tblRevisionAudit.RevisionAudit,
       tblRevisionAudit.fldDate,
       Format([fldDate],"yyyy") AS [Year],
       tblRevisionAudit.Sort,
       tblRevisionAudit.[Initials 1],
       tblRevisionAudit.[Initials 2],
       tblRevisionAudit.P1,
       tblRevisionAudit.E1,
       tblRevisionAudit.P2,
       tblRevisionAudit.E2,
       tblRevisionAudit.[CommentsOnAudit:]
 
FROM   tblRevisionAudit
 
WHERE  (((tblRevisionAudit.RevisionNumberLookup)=[Forms]![frmRevisionAudit]![RevisionNumberLookup])
  AND  ((tblRevisionAudit.BagNumberLookup)=[Forms]![frmRevisionAudit]![BagNumberLookup])
  AND  ((tblRevisionAudit.RevisionAudit)=[Forms]![frmRevisionAudit]![cboRevisionAudit])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2]))
   OR  (((tblRevisionAudit.BagNumberLookup)=[Forms]![frmRevisionAudit]![BagNumberLookup])
  AND  ((tblRevisionAudit.RevisionAudit)=[Forms]![frmRevisionAudit]![cboRevisionAudit])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![RevisionNumberLookup]) Is Null))
   OR  (((tblRevisionAudit.RevisionNumberLookup)=[Forms]![frmRevisionAudit]![RevisionNumberLookup])
  AND  ((tblRevisionAudit.RevisionAudit)=[Forms]![frmRevisionAudit]![cboRevisionAudit])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![BagNumberLookup]) Is Null))
   OR  (((tblRevisionAudit.RevisionAudit)=[Forms]![frmRevisionAudit]![cboRevisionAudit])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![RevisionNumberLookup]) Is Null)
  AND  (([Forms]![frmRevisionAudit]![BagNumberLookup]) Is Null))
   OR  (((tblRevisionAudit.RevisionNumberLookup)=[Forms]![frmRevisionAudit]![RevisionNumberLookup])
  AND  ((tblRevisionAudit.BagNumberLookup)=[Forms]![frmRevisionAudit]![BagNumberLookup])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![cboRevisionAudit]) Is Null))
   OR  (((tblRevisionAudit.BagNumberLookup)=[Forms]![frmRevisionAudit]![BagNumberLookup])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![RevisionNumberLookup]) Is Null)
  AND  (([Forms]![frmRevisionAudit]![cboRevisionAudit]) Is Null))
   OR  (((tblRevisionAudit.RevisionNumberLookup)=[Forms]![frmRevisionAudit]![RevisionNumberLookup])
  AND  ((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![BagNumberLookup]) Is Null)
  AND  (([Forms]![frmRevisionAudit]![cboRevisionAudit]) Is Null))
   OR  (((Format([fldDate],"yyyy"))=[Forms]![frmRevisionAudit]![cboYear])
  AND  ((tblRevisionAudit.Sort)=[Forms]![frmRevisionAudit]![cboSort])
  AND  ((tblRevisionAudit.[Initials 1])=[Forms]![frmRevisionAudit]![cboInitials1])
  AND  ((tblRevisionAudit.[Initials 2])=[Forms]![frmRevisionAudit]![cboInitials2])
  AND  (([Forms]![frmRevisionAudit]![RevisionNumberLookup]) Is Null)
  AND  (([Forms]![frmRevisionAudit]![BagNumberLookup]) Is Null)
  AND  (([Forms]![frmRevisionAudit]![cboRevisionAudit]) Is Null))
   OR    .....etc.

The sql statement runs on for about 5 or 6 times this length. I just wish I new how to restate everything so this crazy cascade doesn't happen. Like I said, right now in the query design view, under, say the field RevisionAudit, that is pulling from tblRevisionAudit, in the criteria section I am putting [Forms]![frmRevisionAudit]![cboRevisionAudit] Or [Forms]![frmRevisionAudit]![cboRevisionAudit] Is Null, so that of course when my combo box on my main form is empty, then the subform will display all records for that combo box. But since I have 8 other fields I'm doing something similar with, when I close the query, Access is creating a slew of rows to deal with every situation.

Ultimately I just want my 7 combo boxes on my main form to be able to filter my subform in a bunch of combinations, including returning all results for a blank combo box.

I just hope there's a simple solution that I'm overlooking. The database is working fine if once I close the query I don't touch it again. But I do NOT want a database tetering on the edge of disaster with one false move lol.

[TheSmileyCoder]

Well I would a different approach.
To each of your textboxes on the form, I would add:

Private Sub Filter_tb_Title_AfterUpdate()
    RefreshDocDisplay
End Sub

Where RefreshDocDisplay is a procedure on the same form, building and applying a filterstring to the subform.

For each of the textboxes in question, it will go through it, and decide whether or not to add it to the string (for instance it won't add it if textbox/combobox is null). When the build is complete, it will apply the string.

In case your wondering the FilterCount is used to keep track of whether or not to add the " AND " part. I hope this can help you!

Private Sub RefreshDocDisplay()
'Writes the Filter string to be used on subform
 
'Init variables
    Dim strFilter As String
    Dim FilterCount As Integer
    FilterCount = 0
 
If IsNull(Me.Filter_tb_DocID) Then
    'Noting
    Else
    'Set string
    If FilterCount > 0 Then
        strFilter = strFilter & " AND "
    End If
    strFilter = strFilter & "(([tx_DocID] like '*" & Me.Filter_tb_DocID & "*'))"
    FilterCount = FilterCount + 1
 
End If
 
If IsNull(Me.Filter_cmb_Binder) Then
    'Noting
    Else
    'Set string
    If FilterCount > 0 Then
        strFilter = strFilter & " AND "
    End If
    strFilter = strFilter & "(([ID_Binder]=" & Me.Filter_cmb_Binder & "))"
    FilterCount = FilterCount + 1
 
End If
 
If IsNull(Me.Filter_tb_Title) Then
    'Noting
    Else
    'Set string
    If FilterCount > 0 Then
        strFilter = strFilter & " AND "
    End If
    strFilter = strFilter & "(([tx_Title] like '*" & Me.Filter_tb_Title & "*'))"
    FilterCount = FilterCount + 1
 
End If
 
If IsNull(Me.Filter_tb_Version) Then
    'Noting
    Else
    'Set string
    If FilterCount > 0 Then
        strFilter = strFilter & " AND "
    End If
    strFilter = strFilter & "(([tx_Version] like '*" & Me.Filter_tb_Version & "*'))"
    FilterCount = FilterCount + 1
 
End If
 
 
If FilterCount > 0 Then
    'One or more filter criterea set
    Debug.Print strFilter
    Me.subFrm_ShowDocsFiltered.Form.Filter = "(" & strFilter & ")"
 
    Me.subFrm_ShowDocsFiltered.Form.FilterOn = True
    Else
    Me.subFrm_ShowDocsFiltered.Form.FilterOn = False
 
 
End If
 
End Sub

[NeoPa]

Definitely the way to go :)

I generally add the " AND " bit at the front in all circumstances, then simply use Mid(strFilter, 6) just before applying it (wherever that may be in the code).

[wordbrew]

Thanks so much Smiley, I'm going to try and dig into this today. I'm glad it's a VBA workaround cause I really need to keep honing my skills on it (which are minimal). To make sure, I keep my subform still based on the query I created, and I still put the criteria in the fields in the query design view, such as Forms!frmRevisionAudit!cboRevisionAudit (so that they still take their direction from the combo box on the main form) but I don't worry about all the other Null business. I let the VBA code handle all of that?

Thank again. I'll get crackin. And thanks NeoPa for chippin in too!

[NeoPa]

You're welcome.

I'm not sure I understand your clarification question too well, but as a general rule, the design of the query itself would still contain any references that are generally required (not dependent), whereas the code would be used for adding items that depend on things.

IE. If you had a table of animals, but you had a report that showed all elephants between the largest and smallest values entered on a form, you would design a query, based on the table, which had designed selection criteria of Type='Elephant', but the form's code would build and add a filter of something like Between Forms!YourForm.Smallest And Forms!YourForm.Largest, possibly adding it only if both entries had values entered.

[wordbrew]

I've put in the VBA code suggested by TheSmileyOne, but I think I'm just so new to the concept of filtering via VBA that I'm just fudging up somewhere. I'm including the database here, with the code i've put in. If someone could take a look and tell me where I'm going utterly wrong I would appreciate it. Hopefully I was getting close and just missing a key concept. But if I was way off base, then please giggle away. :)

[TheSmileyCoder]

Alot of place you had written Me.Filter.RevisionLookup, the .Filter should not be there.

I fixed it for the first combobox, the revision lookup, then you can work for there.

Note that I switched the syntax from using a Like "*"..... to =, since your using a combobox with ID fields.

I removed all the criteria from the query of the subform, the only criteria you should use there, are ones NOT related to the form.

You also (as far as i know) do not need to requery after setting the filterstring, so I removed that for the first combobox bit of code as well.

Have a look, see if you can make it work.

[wordbrew]

Alright, I've tried replacing and configuring the other VBA from what i could divine from your changes. I even went back and changed the names of the other boxes so I could make things look consistent. But I'm just not seeing something.

I don't know if it's because two of my combo's are based on value lists cmb_RevisionAudit, and cmb_Sort and/or my cmb_Year needs to sort just the year in the fldDate field in the query, which it was doing before in the query, but now the year format probably needs to be put in the VBA filter.

I really appreciate everything I've been helped with, and I don't expect to have it tinkered with and handed back to me working lol, if I could just get a few tips on where I'm going wrong that would be wonderful. You've already been awesome expending valuable time on my problem.

[TheSmileyCoder]

I made some changes to the code, and where I made changes I left a short comment. If any of those are unclear, just ask.

From what I could test it works now (except your Clear buttons, but you only need to add a refreshDocDisplay line to those to make em work.)

The reason why I prefix my controls with certain names is:
1) Because then I know if im dealing with a combobox or a textbox, and how to handle each case in code
2) Because if I im writing code, and trying to remember the name of my combobox, I can start by typing me.cmb.... and then VBA will automatically show me the list of comboboxes on my form.

[wordbrew]

Smiley this is incredible! Thank you so much. You went far above and beyond what I could have ever asked. It's so amazing to have a community of programmers so willing and able to give their time to help make the rest of us better.

Lol. And thank you for the comments in the VBA, this way I can try to understand some of the key concepts so I can grow in my understanding of VBA and improve in the future. At least I was on the right track as far as trying to implement your changes, so I don't feel like such a dunderhead. It looks like the only 2 things I'll have left is trying to make the cmb_Year combo work to filter for it's "All" selection. That's the only thing that is causing a bug at this point. But all the separate year selections work perfectly.

The other thing is my button to generate my rptRevisionAudit that is based on the same query that my subform is. Since the subform is now filtered via VBA (thank you truckloads!) I'll have to see how to do the same thing for the report. So that once they have fitered their selection on the main form, all they have to do is hit the generate report button, and only their selection appears in the report. This was working before, but since the whole way my subform is being filtered had to be reworked, this obviously now returns all results since it's query doesn't have anything under criteria anymore. It's so funny, I originally had thought my database was done until I realized I needed to be able to filter for year, in order to make the database perpetual, and not have to have old data erased just to make room for the new year. And that extra bit of information was what pushed the query over the edge. Well here's to learning something new!

Again, thank you so much for everything you have done! I just hope I can keep improving to the point that I'm able to pay all this forward, and be able to help someone else on bytes in the future.

[TheSmileyCoder]

Your welcome.

If you change the code of your report button to include the filter string, it will open with the filter applied. Example below:

stDocName = "rptRevisionAudit"
        If Me.frmRevisionAuditSubform.Form.FilterOn = True And Me.frmRevisionAuditSubform.Form.Filter <> "" Then
            DoCmd.OpenReport stDocName, acPreview, , Me.frmRevisionAuditSubform.Form.Filter
        Else
            DoCmd.OpenReport stDocName, acViewPreview
        End If

I will go far in helping someone if they satisfy some simple criteria:
1) Its obvious they have used effort themselves, both in solving the problem, but also in making a good thread. People who don't even bother to read through their own post before pressing submit, and thus leaving half-finished or unclear sentences just ******* me off. In my oppinion a preview should be mandatory before you could hit submit.
2) They make a well described problem/question
3) They have thought enough about their question/problem, to supply all the relevant facts.
4) Its something they NEED, or are truly interested in solving for learning. I dislike people showelling their homework onto forums like this, without even having looked at it themselves first.

You satisfied all of the above.

[wordbrew]

Smiley you are too kind. Gentleman and a scholar truly. And I'm grinning bigtime because I inserted your code into my call button for the report and it is working beautifully again. So thank you thank you. You rescued my database. It's too bad the regular query couldn't handle more information or I would have been fine in the first place. But, then again, if it had worked, I wouldn't have found a better workaround via your method of using the Filter in VBA. So now I have those tools to dissect and understand and use in the future. So thank you again. Whenever you have time, I would love if you could explain the logic in the code you gave me for the report. I understand the "If Me.frmRevisionAuditSubform.Form.FilterOn = True" part, that if the condition is met then return these results, I'm just curious about the <> "" part for the And Me.frmRevisionAuditSubform.Form.Filter <> "". It all works great, I just want to know why it works. :)

My other question is if you could offer your insight to my "All" selection problem in the year combo box and what route you might have gone to solve it. I tried to include an OR statement along the lines of strFilter = strFilter & "(([Year]=" & Me.cmb_Year & "))" Or strFilter & "(([Year]=" & Me.cmb_Year ="All" & "))" , but that didn't fly at all lol.

Hey, at least I'm getting to the point where I can look at VBA and it doesn't look a Martian scribbled a cypher on some toilet paper. But I have a long way to go. I solved my problem by going ahead and using the null properties already built into the filter string. So where I had an AddItem "All" set for the cmb_Year combo box, I changed that to AddItem "". And this is working great. And I could keep it this way and simply add text on the main form along the lines of "Leave any selection box blank to return all results" or some such wording.

I was curious if I had chosen to stick to trying to keep "All" as a selection, would there have been a way to use AddItem "All" but make VBA see "All" as a Null value? Just curious.

If you ever have time to offer me any of the insights I would really appreciate it. But my database is in great shape now all due to your efforts. So thanks again! It means a whole lot.

[TheSmileyCoder]

Well I wasn't 100% sure if Access could create a situation in which FilterOn would be true, but the filterstring would be non-empty. Therefore I checked for both conditions, requiring the filterOn property to be true and the Filter string to be different (the <>) from an empty string ""

I was actually a bit surprised that the emptystring you introduced into your combobox was treated as Null by the code, so I learned something too :)

I would modify the part of the code handling the year part to:

If not IsNull(Me.cmb_Year) AND Me.Cmb_Year <>"ALL" Then
    'Set string
 
    If FilterCount > 0 Then
        strFilter = strFilter & " AND "
    End If
    strFilter = strFilter & "(([Year]=" & Me.cmb_Year & "))"
    FilterCount = FilterCount + 1
 
End If

So now it has to be different from Null and be different from ALL (Basicly apply no year filtering if its "All".)

I also looked at your form_Load while I was in the db. A suggestion:

Dim intCurrentYear As Integer
Dim intI As Integer
Dim intFirstYear As Integer
 
    intCurrentYear = Format(Date, "yyyy")
 
'Get first year
 
    intFirstYear = Year(Nz(DMin("fldDate", "tblRevisionAudit"), Date))
 
Me.cmb_Year.RowSourceType = "Value List"
Me.cmb_Year.AddItem "All"
 
For intI = intFirstYear To intCurrentYear
  Me.cmb_Year.AddItem intI
Next
 
 
 Me.cmb_Year.DefaultValue = intCurrentYear

Now you can only select as far back as your data actually goes. No need to be able to select 2005, if you have no data for 2005.
If you do need to be able to select a date thats further ahead then the current date (for planned revision/audit) just change the For line to:
For intI = intFirstYear To intCurrentYear+5
to get 5 "extra" years.

[NeoPa]

I would echo Smiley's comments about the sort of poster one is happy to put a bit of extra effort in for. It's like they always say - You make your own luck. In this case you're lucky because of how you've behaved. If everyone showed your attitude we'd all be a lot happier bunnies.

Just a quick tip to throw in while I'm here pertaining to an earlier question about how to go about resolving such complicated issues in the first place :
As a general rule of thumb, start simple and build up when you have the general concepts working.

A couple of other items for debugging, that may prove useful :
Debugging in VBA

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[wordbrew]

NeoPa

Thank you for your advice. I shall definitely try it. And I myself am lucky to have the experience and flat out crazy skills of guys like you and Smiley, as well as ADezii who has helped me in the past, to call upon. I mean, you know, i just kind of imagine the kind of poster I would be happy to help, and try to follow that example. When you're calling upon other people to give up their valuable time to help you out, essentially out of kindness and charity, then you can't be anything but humble and hopeful. So thanks again guys. I just geek out and get excited when some of these concepts, especially VBA, start to gell in my head and make sense, and I can carry forward some of the tricks I've learned.

Smiley,

Lol, when you threw in a bit of extra code for me cause you just happened to be looking at it, you solved an issue that was bugging the heck out of me in a major way. I implemented your changes for the year combo box, as well as making it handle the "All" value, and it's awesome. Now there aren't superfluous years that pop into the combo box and are unneeded. Having it dynamically populate the box "only" with years that are actually used in the system is perfect, and so much cleaner to look at. You are making me look much better than I deserve.

I've created another form (frmReplacedRemoved) in the database that handles a different job function in the Flight Manuals department (I'm actually a Flight Crew Van Coordinator, I'm just helping them out cause they asked nicely. They sorely need a better system of tracking then an excel sheet lol), and happily I've been able to use the coding knowledge you've introduced me to, and I've got it filtering pretty awesome. For some reason the year box wasn't working from the VBA filter string (I renamed the value to fldYear in the query, per your suggestion to avoid using Year), so I surrounded it with single quotes, and that solved my problem. I'm not sure why this would solve it, since in the other form's VBA it isn't surrounded by single quotes, and the year boxes in both are essentially the same, but who knows? The only thing I'm having trouble with is my cmb_Gateway combo isn't working via the VBA filter. I'm unsure why since it doesn't seem to be any different from what my other combos are doing and the type of information they are pulling, but I'm determined to work the problem out for myself before I need to call on the experts lol. I shall conquer it yet!

Thanks again guys.

[NeoPa]

Good for you.

Remember, we're here if you get stuck though :)

Good luck.

[MrDeej]

I dont know if this helps, but in our system we use alot of query-steps.

For example we have query1 which filter some fields. Then on query2 we have used query1 instead of tables as recordsource. Then we can do additional filtering or adding of tables to query1. And so on.

On our biggest query which collect data from many many tables we have maybe 7 steps.

[NeoPa]

That can be helpful, but for particularly large or complex setups, subqueries (Subqueries in SQL) may work better. This is due to the optimisations. Saved QueryDefs (as you describe) have theirs saved with them, whereas new queries, whether they involve subqueries or not, must get them worked out first before running.

This often means the saved QueryDefs have an advantage (first time they're run as subqueries), but in some cases the optimisations are determined in circumstances so different from the current usage, that they're worse than useless.

In general, once they have been worked out, I would expect that subqueries done in SQL would never be less efficient than those using saved QueryDefs for their source. I hope that make sense.

Of course that can make the maintenance a little more complicated so I don't recommend strongly. I simply suggest you bear in mind.

[MrDeej]

Nice tip NeoPa, i have downloaded your example and bookmarked this thread for future use.

[NeoPa]

Thx Deej. Always nice to feel you've helped :)