Re: Complex query criteria - desperate appeal

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

Date: Tue, 16 Nov 2004 13:21:46 -0800

Ted:

Thanks very much for the posts, it is greatly appreciated.

I'm curious about one thing: I made an ASP website using an Access DB
backend, and made a custom search page using exactly what I'm talking about,
but each drop-down list also had an "ALL" choice, which I believe passed the
"%" wildcard to the search. Why can we not use a "*" or "%" value in the
form, which passes a wildcard to the query as opposed to a Null?

In the page (which, again, is using ASP and MS Access), 10 different
criteria listboxes have the ALL choice, and the value passed to the query is
%:

<option value="%">ALL</option>

The ASP in the query language (on the results page) is:

SELECT * FROM SearchPage WHERE (Country LIKE '%::Country::%' AND Sale2 LIKE
'%::Sale2::%' AND Whatever LIKE '%::Whatever::%' AND etc. etc.') ORDER BY
::SortOrder:: ASC, Name ASC"
fp_sDefault="Country=&Genre2=&Allegiance=&Association=&Association2=&SortOrd
er="
fp_sNoRecords="NO ITEMS FOUND: TRY OTHER CRITERIA"

Now, I know this is Frontpage extensions and ASP, but it IS a field passing
the wildcard criteria to SQL-based language in ASP. Is there really no way
to pass a "*" or some kind of "anything" criteria to a parameter in JUST
Access? I mean, this would be a magic bullet.

Thanks again.

"Ted Allen" <TedAllen@discussions.microsoft.com> wrote in message
news:B635E8EC-C7CF-4AEF-BED6-32540F46DA1D@microsoft.com...
> pps, okay, one more thing. One of the things that I use most often that I
> didn't mention before is to populate list boxes with data (such as all
> project managers, or project categories), which allows users to select
> multiple options. I then build a string of the selected ID's and use
syntax
> such as:
>
> "Myfield In (" & strList & ")"
>
> to limit the query to the selected ID's from the appropriate field.
>
> I'm not sure if this would be helpful in your case, but I find it very
> useful in that it allows the user to come up with more combinations. Post
> back if you are interested and I can post some vba syntax for building the
> string from the list box.
>
> -Ted Allen
>
> "Ted Allen" wrote:
>
> > 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

  • Programatically Changing Query Criteria
    ... Save your query in SQL. ... the following to determine your criteria. ... Dim strSQL As String ... MsgBox "There was a problem building the SQL String" ...
    (microsoft.public.access.macros)
  • Re: Passing multiple strings from a text box to a select query
    ... Search Criteria database page on your website have given me lots of new ... Will a filtered form allow a subform that displays records from a query ... The article explains how to create the string to use as the WhereCondition ... This allows me to enter "seat" in the text box and return ...
    (microsoft.public.access.forms)
  • RE: List Box and Query
    ... I would not put any criteria in qry_Suppliers. ... build up a Where string and then apply that "WHERE" criteria to a Select ... Query (qrySupplierTEST) to get my desired records. ...
    (microsoft.public.access.formscoding)
  • 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: Database Results Using Page Variable
    ... You can modify the Pick list example to use a hyperlink to pass the query (your criteria) ... Right above the 1st grey code just enter the ASP code to get the query value from the sending page ... But the database results in the include page should show ...
    (microsoft.public.frontpage.programming)