Re: bypassing some of the parameters in a parameter query

From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 09/23/04 

Date: Wed, 22 Sep 2004 20:10:22 -0500

Dear Lorian:

It's hard to imagine what's going on with your system. Post the SQL
where you're having this problem and I'll look!

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

On Wed, 22 Sep 2004 10:11:05 -0700, Lorian
<Lorian@discussions.microsoft.com> wrote:

>Well, 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:
>
>> Dear 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
>> <Lorian@discussions.microsoft.com> wrote:
>>
>> >Wheeeeee! 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:
>> >
>> >> 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
>> >> <Lorian@discussions.microsoft.com> wrote:
>> >>
>> >> >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?]));
>> >> >
>> >> >"Lorian" wrote:
>> >> >
>> >> >> 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!
>> >>
>> >>
>>
>>

Loading