Complex query criteria - desperate appeal

From: rgrantz (r_grantz_at_hotmail.com)
Date: 11/15/04 

Date: Mon, 15 Nov 2004 15:56:04 -0800

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: Printing labels for alumni database for 21 and older
    ... I forgot one more criteria, ... query builder again, but I don't know what to put in the CRITERIA field. ... Address in addtiontion to the criteria you have under Graduating Class Year. ... Married Name and then Home Street Address and then Home City and then Home ...
    (microsoft.public.access.reports)
  • RE: using the results of a function for Criteria
    ... In query builder when you put in ... 'red' and 'green' for criteria it writes in sql veiw as "WHERE ... You need to use where clause of the docmd.open report ...
    (microsoft.public.access.queries)
  • input
    ... In the query builder, simply put the following in the ... criteria line under GPA: Between And ... >want to extract students whose GPA is between an input ...
    (microsoft.public.access.formscoding)
  • Re: Getting a custom function to work in a criteria
    ... the query builder at build time, and I do not know of a way to add custom ... quotes in the field's criteria nothing is returned. ... Function BaseFacility() As String ... Dim frmReport As Form ...
    (microsoft.public.access.queries)
  • Re: Help with blank records
    ... If you put criteria on the same row in the query builder, ... then place the criteria for password on the next row. ... (I think you will have to place the teamleader criteria on both lines.) ... select all records where TeamLeader=AND UserName Is Null ...
    (microsoft.public.access.queries)