RE: Complex query criteria - desperate appeal

From: Ted Allen (TedAllen_at_discussions.microsoft.com)
Date: 11/15/04 

Date: Mon, 15 Nov 2004 15:47:02 -0800

I don't know of an easy way to do what you are looking to do in Access. I
think that the earlier advice to build the sql string in code may solve your
problem. It should result in a much simpler sql statement because you won't
need all of the OR conditions for null cases. Instead, the VBA code would
look at each control value and ignore it if it is null, or append the
appropriate SQL string if it is not.

There are many ways that you could approach the VBA code, but I'll show a
few typical lines (you'll need a reference to DAO):

Dim db as DAO.Database
Dim qdf as DAO.QueryDef
Dim strSQL as String
Dim strWhere as String
Dim StrAnd as String

StrWhere = ""
StrAnd = ""

If Nz(Me.Ctrl1,"") <> "" Then
  strWhere = " QueryField1 Like '*" & Me.Ctrl1 & "*'"
  strAnd = " AND"
Endif

If Nz(Me.Ctrl2,"") <> "" Then
  strWhere = strAND & " QueryField2 Like '*" & Me.Ctrl2 & "*'"
  strAnd = " AND"
Endif

'Go through the rest of the ctrls

StrSQL = "Select ... (Enter Your Base Select Statement)"
StrSQL = strSQL & " WHERE" & strWhere

Set db = CurrentDb
Set qdf = db.QueryDefs("YourQueryName")
qdf.sql = strSQL

set qdf = nothing
set db = nothing

Note though, that you may not be able to change the sql of a query that is
bound to an open subform, so you may have to temporarily change the data
source of the subform to nothing, then change the query, then change the data
source back. I'm not sure, I haven't tried that before.

Also, there are obviously other ways that you could loop through the
controls, and set the criteria, but it would depend somewhat on the data
types and other things. Also, the above assumes that you are adding LIKE
criteria in text fields, you would have to modify these somewhat to give the
exact criteria that you would be looking for.

I think that I have seen some earlier posts with precoded query forms that
would probably have some very good sample code. You may want to do some
google searches of the Access newsgroup to find some if you do go that route.

HTH, Ted Allen

"rgrantz" wrote:

> Okay, I've got a pretty big problem here;
>
> I'm trying to get rid of about 45 extra minutes of crap labor for 4 people
> at once, as well as cutting down on duplicative data and reporting time.
> I'm almost there now, and the 4 people are ecstatic about what's here so
> far. However, there is ONE thing missing that I'm having a hard time doing:
>
> This manager I'm making this for has about 13 different criteria he likes to
> change and see new data for (customer name, who entered it, what date it was
> received, what date range it was entered, what date range it was processed,
> who processed it, what machine it was made on, who packaged it, who shipped
> it, etc. etc.).
> Currently he uses a combination of the Autofilter utility and the
> Dcount(Data!...etc.) formula in Excel, which returns data based on any
> criteria put in an entire row, which is nice. He can change any criteria
> along the entire row, adding some, changing some, deleting others, etc., and
> Excel just uses the whole row to continuously dynamically change the records
> returned as the criteria change (ie. he enters a particular name for
> OrderEnteredBy, and only sees those records, then he enters a name in
> OrderShippedBy, and sees only records with BOTH criteria, then he enters
> another one, etc. etc.., and when he deletes a criteria, the records reflect
> the additional records that no longer have criteria there, but still meet
> the OTHER criteria. You get what I'm saying here, right? It's pretty much
> being able to stack 13 criteria, with each criteria field also having an
> "ALL" (or technically, "NONE", for no criteria) choice in addition to actual
> criteria to apply.
>
> I need to do this on a form, and duplicate how the data changes after each
> criteria is altered. However, there are too many criteria fields,
> apparently, for Access to handle using the query builder. I can use the
>
> [forms]![FormName]![UnboundCriteriaFieldName]
>
> in the criteria cell and
>
> [forms]![FormName]![UnboundCriteriaFieldName] is null
>
> in the "Or" cell of the query builder, and this works great for about 3
> fields. He has too many that he wants to see, though, and Access returns
> errors about the query being too complex, or the form starts returning
> unpredictable results.
>
> It seems very strange to me that Access can continually compound filters on
> a form using the "filter menu" for different fields, and that Excel can do
> all this with one simple formula, but that I can't get an Access form to do
> it.
>
> Someone, please help me, this thing I'm making is PERFECT and has solved 12
> big problems, but if the manager can't get this ability to change these
> multiple critieria and see the records change as he does so (and be able to
> delete criteria as well as change them), then the whole thing's a no go.
>
> All I've been able to think of so far is to make 500 different queries and
> run a Select Case on the AfterUpdate of every unbound criteria control,
> which cycles through to see which fields are null and which have criteria
> and run the corresponding query to refresh the form with. This would be
> insane. Someone mentioned using a SQL string and code to change the query
> results, rather than using specific queries, but I either don't know how to
> do this or the SQL string would be too complex too (don't Access queries
> made using the query builder represent SQL strings anyway?)
>
> Someone, please help, I can't help thinking there MUST be a way to do this.
> I'm sorry to sound deperate, but the idea of canning this thing and having 4
> people spend an extra 45 minutes a day on completely duplicative data, w/
> more possibilities for error, less flexibility for reporting, etc., is
> driving me crazy. It's not helping the 4 people out either. And everyone's
> on board, even this manager, if I can just get this one little gimmicky
> thing going.
>
>
> Thanks for ANY help, I would really appreciate it. I can buy lunch for
> anyone who helps me come up w/ a solution.
>
>
>
>

Relevant Pages

  • Re: Using combo box to search form
    ... "Jeff Boyce" wrote: ... Since you "started adding" additional criteria, ... about SQL syntax to know how to form a grammatically-correct SQL statement. ... One way to do that is to see what Access generates when you build a query. ...
    (microsoft.public.access.formscoding)
  • Re: Adjusting SQL in Query
    ... from the RESPEL table which satisfy the criteria in the PRICELIST Table. ... RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST = ... Subject: Adjusting SQL in Query ...
    (microsoft.public.access.queries)
  • Re: Using combo box to search form
    ... Since you "started adding" additional criteria, ... about SQL syntax to know how to form a grammatically-correct SQL statement. ... One way to do that is to see what Access generates when you build a query. ... Dim strWhere As String ...
    (microsoft.public.access.formscoding)
  • Re: Criteria linked to form combo Yes/No or All expression help
    ... The way I'd debug it would be to start w/ one OR clause in the criteria. ... Run the query & see if it works for that criteria. ... I followed your suggestion of pasting the sql, ...
    (microsoft.public.access.queries)
  • RE: Complex query criteria - desperate appeal
    ... The following will search for query by form: ... > appropriate SQL string if it is not. ... > Dim qdf as DAO.QueryDef ... > criteria in text fields, you would have to modify these somewhat to give the ...
    (microsoft.public.access.queries)