bypassing some of the parameters in a parameter query

bypassing some of the parameters in a parameter query

Post by TG9yaWF » Wed, 22 Sep 2004 04:31:04


Hi - I'm creating a parameter query that will ultimately have approx 6
parameter prompts. The user may only know the answers to 1 or 2 of the
prompts. How can I set up the criteria so that the query will return all the
records that meet the remaining criteria? I used the WHERE function mentioned
in another post, and perhaps I just got it wrong, but it's not returning the
results I need. Please help if you can - thanks in advance!
 
 
 

bypassing some of the parameters in a parameter query

Post by Rick » Wed, 22 Sep 2004 04:54:50

in the criteria you would do something like...



like [EnterName] & "*"

like [EnterDepartment] & "*"

etc.

If left blank, Access will only see "*" which will pull all records.

Rick B




the
mentioned
the

 
 
 

bypassing some of the parameters in a parameter query

Post by Um9ieW4gSC » Wed, 22 Sep 2004 05:03:13

This is the advice I got on the same question you had, that ultimately worked
for me.

Open your query in design view, then choose "SQL View" from the "View" menu.
Look for the word "WHERE" in the SQL view. That's what my example is - the
WHERE clause of a query. Using BETWEEN ... AND the WHERE clause will end up
looking something like ... WHERE ([DateField] BETWEEN [Start Date?] AND [End
Date?]) OR ([Start Date?] IS NULL AND [End Date?] IS NULL)
 
 
 

bypassing some of the parameters in a parameter query

Post by TG9yaWF » Thu, 23 Sep 2004 00:57:01

Thank you, Rick and Robyn - I've tried both of these methods, and so far am
ending up with the dreaded result of "it works sometimes", but i've not yet
been able to figure out the reasoning behind when it doesn't work. A question
on the WHERE statement: does it only go at the beginning of the string of
criteria, or in front of the criteria for each field? I have tried it both
ways, altho it does look like it only goes once at the front. Thanks for the
help!
 
 
 

bypassing some of the parameters in a parameter query

Post by Tom Elliso » Thu, 23 Sep 2004 03:58:53

Dear Lorian:

For each parameter, you can test:

WHERE ([SomeColumn] = [Enter Parameter] OR [Enter Parameter] IS NULL)

It does not have to be "=" in the above, but any comparison operator.

Be sure each condition like the one above is in parentheses, as I have
shown. Between conditions use AND or OR as desired.

It works like this. If the user enters nothing, the value of that
Parameter is NULL. According to the logic, the phrase above is always
true when that happens, so nothing is filtered out. When the user
enters something, then the phrase is true only when the left hand
comparison succeeds. This should be just what you want.

A comparison with wildcards can be "too inclusive." If they type in
"John" and you search for "John*" then you also get "Johnson" and
"John Smith" which, if that's not what you meant, well, too bad. Also
a LIKE comparison cannot test for less than or greater than (not that
I'm saying that's what you want - I just don't know exactly what you
want, but I'm trying to accomodate a wide range of possibilities and
give a flexibly general answer.)

If you have trouble with this, post what you have without making an
attempt to handle the "omitted parameter" capability and I'll try to
add it for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 21 Sep 2004 08:57:01 -0700, Lorian
 
 
 

bypassing some of the parameters in a parameter query

Post by TG9yaWF » Thu, 23 Sep 2004 05:23:08

Thanks, Tom - i'll try that and let you know...

Lorian
 
 
 

bypassing some of the parameters in a parameter query

Post by TG9yaWF » Thu, 23 Sep 2004 06:15:01

Sorry, I'm still not able to get it to work. I tried what you suggested, Tom,
but then i got an error message that the WHERE expression was undefined (I've
tried it before, and even tho the query didn't work as I had wanted it to, I
didn't get that message before, so I'm not sure what that's about). Anyway,
here's the SQL of what I have, without the "omitted parameter" capability,
like you suggested. Thanks for any help you can provide:

SELECT [Product Keys].[Content/Project File Name], [Product Keys].[Source
Origination], [Product Keys].[Information Product Type], [Product Keys].[CMS
Product Version], [Product Keys].[Product Line], [Product Keys].[User
Profile(s)], [Product Keys].[Special Notation]
FROM [Product Keys]
WHERE ((([Product Keys].[Content/Project File Name])=[Filename?]) AND
(([Product Keys].[Source Origination])=[Source?]) AND (([Product
Keys].[Information Product Type])=[Document Type?]) AND (([Product Keys].[CMS
Product Version])=[CMS Version?]) AND (([Product Keys].[Product
Line])=[Product Line?]) AND (([Product Keys].[User Profile(s)])=[User
Profile?]) AND (([Product Keys].[Special Notation])=[Special Info?]));
 
 
 

bypassing some of the parameters in a parameter query

Post by Tom Elliso » Thu, 23 Sep 2004 06:38:12

Dear Lorian:

SELECT [Content/Project File Name], [Source Origination],
[Information Product Type], [CMS Product Version],
[Product Line], [User Profile(s)], [Special Notation]
FROM [Product Keys]
WHERE ([Content/Project File Name] = [Filename?]
OR [Filename?] IS NULL)
AND ([Source Origination] = [Source?]
OR [Source?] IS NULL)
AND ([Information Product Type] = [Document Type?]
OR [Document Type?] IS NULL)
AND ([CMS Product Version] = [CMS Version?]
OR [CMS Version? IS NULL)
AND ([Product Line] = [Product Line?]
OR [Product Line?] IS NULL)
AND ([User Profile(s)] = [User Profile?]
OR [User Profile?] IS NULL)
AND ([Special Notation] = [Special Info?]
OR [Special Info?] IS NULL);

Zup?

There's a lot of stuff there. If I didn't type everything exactly,
perhaps you can see what I meant and fix it.

Wouldn't this be better off a form interface, where the user could
change one parameter and run it again, rather than retype every one to
make one change or correction? You could also use combo boxes that
list the valid values of each parameter. In my experience, that's so
much better.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 21 Sep 2004 14:15:01 -0700, Lorian
 
 
 

bypassing some of the parameters in a parameter query

Post by TG9yaWF » Thu, 23 Sep 2004 07:02:23

hanks a lot, Tom - i'll let you know how it goes. As for your suggestion, is
that something I can do on the 'front side' without getting into coding? I'm
strictly working from the front side. I had originally started to try to run
the query from a form, but it quickly appeared that i would have to get into
code to do that. But if it's possible, I'd love it. Thanks - Lorian

"Tom Ellison" wrote:

 
 
 

bypassing some of the parameters in a parameter query

Post by TG9yaWF » Thu, 23 Sep 2004 07:05:11

heeeeee! It's working! I hadn't pasted your SQL in yet, but I was reading
tons of other posts, and saw that the WHERE doesn't work in Design view, only
SQL view, so I worked with that for a bit, and it's working! Thanks a bunch!
But i'm still very interested in knowing if I can run this entire thing from
a form instead even tho I'm only working from the front end.
Thanks again!
Lorian


"Tom Ellison" wrote:

 
 
 

bypassing some of the parameters in a parameter query

Post by Tom Elliso » Thu, 23 Sep 2004 12:45:58

ear Lorian:

Well, WHERE does work in design view, but having a lot of ANDs and ORs
can be a mess. In fact, what is simple and direct logic in SQL View
can be a real mess when you see it in design view. And if you switch
back to SQL View after it makes a mess of it in design view, it can be
an incredible mess. However, this doesn't mean you can't do something
like this in design view. If you want to learn how, just change this
query to design view and see if it makes sense to you. If so, then
you can certainly work that way.

Your question: "[can] I . . . run this entire thing from a form
instead even tho I'm only working from the front end."

I don't really know what you mean. If you save the query or make it a
form's RecordSource, or a combo or list box's RowSource it will run
there.

Oh, I know! You're reacting to my suggestion to have it run from a
form instead of with having 7 parameters! Sure!

Each parameter would be a control, likely a text box or combo box,
depending on whether you can create a list of possible choices using a
query on the various columns on which you're filtering. That saves
the users the pain of trying to make sure everything is spelled
exactly. Instead of a parameter entry, like [Filename?] you can refer
to the value in a control:

WHERE [Content/Project File Name] = [Forms]![FormName]![ControlName]

Replace FormName and ControlName with the actual name of your form and
control.

Now, this likely has the same requirement as the parameter that the
user may not have entered a "Filename"

To handle that from a form is very like handling it from a parameter,
except that when there is no value it may be either NULL or an empty
string. So the test for having no entry is a bit different. It comes
out like this:

WHERE ([Content/Project File Name] =
[Forms]![FormName]![ControlName]
OR Nz([Forms]!FormName]![ControlName], "") = "")

This says to convert the value in the control to an empty string if it
is NULL, then tests to see if it is an empty string. That's how it
treats both NULL and empty the same.

This is really just a few minutes extra work, and think of the
dividends. A user can rerun the query with a change of one parameter
without touching the others. For the values in combo box lists, the
user doesn't have to type the whole thing, just scroll or type enough
to get the desired value on the screen and click on it. Saves
misspellings, too.

I hope you'll implement this and enjoy it. NONE of my applications
have any parameter queries any more. This is just so superior to put
them in a form. The user can even review the selections made when the
results are available on the screen, to see if something was forgotten
or entered mistakenly.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 21 Sep 2004 15:05:11 -0700, Lorian
< XXXX@XXXXX.COM > wrote:


 
 
 

bypassing some of the parameters in a parameter query

Post by TG9yaWF » Thu, 23 Sep 2004 23:15:02

hat sounds great, Tom - thanks for the information. After I hook this whole
thing to our intranet page, i'll come back to that task and see if i can get
it to work from a form. Thanks again, and i'll let you know if I run into any
stumbling blocks (gee, that sounds a bit like a threat, doesn't it?!)
:o)

Lorian

"Tom Ellison" wrote:

 
 
 

bypassing some of the parameters in a parameter query

Post by TG9yaWF » Fri, 24 Sep 2004 00:35:16

o, i'm starting on the form, and i have a somewhat related but very
novice-type question: once the user fills in the form and i guess clicks
"Run" or some other button I put on there, can the results also be returned
on a form? Or a report? Or could I even have the results return to a form,
and then have a "Report" button on that form so they can print a report of
the results? (I'd like to be able to return the results on a form because
part of the table is the location of the file they're looking for, and I've
built in a hyperlink to that file so they could go right to it.)

Thanks -

"Tom Ellison" wrote:

 
 
 

bypassing some of the parameters in a parameter query

Post by TG9yaWF » Fri, 24 Sep 2004 00:43:07

o now I have another question...yesterday when I was playing with the query,
I ended up with the "Query is too complex" error, so I just started over, and
it was fine. But this morning, when I tried to open the same one, I'm getting
the error again. How can I avoid this? The SQL view is hugely long, so i
don't want to just post it here, but it's based on using the WHERE that we
'talked' about yesterday.

"Tom Ellison" wrote:

 
 
 

bypassing some of the parameters in a parameter query

Post by TG9yaWF » Fri, 24 Sep 2004 02:11:05

ell, you probably won't be surprised to find I have yet another question. I
created the form which will run the query, but when try to run it, I get the
error message "Undefined function 'WHERE' in expression". The other query I
had created (which now thinks it's "too complex" - see previous post)
contains the 'WHERE' expression and was working fine until yesterday. I
looked at some other posts containing this error, and I'm using the same
machine on which I created the whole shebang, and nothing in code - all from
the user side.

Help, please - thanks!

"Tom Ellison" wrote: