Re: error -2147217904 - how to fix :o( ?

oraustin@xxxxxxxxxxx wrote in
news:1135288951.185848.30520@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:

> sorry david, maybe I am being a bit dim but I don't quite
> understand. If the queries don't have the where clauses then the
> won't work in other circumstances.

If the saved queries don't have WHERE clauses, yes, the saved
queries return all the records.

> Either way - isn't that similar to "dynamic" SQL ( a phrase from
> above) which seems to mean a string of SQL language with a
> variable embeded in it - in this case the value of a control on a
> form

Well, I don't know. When I say on-the-fly SQL, I mean that somewhere
in code, you define the WHERE clause and assign the SQL as a
recordsource for your report or form. This is very different from
embedding parameters or references to form controls in the SQL,
which is something I avoid doing (except in the one or two
situations where it is pretty much required; those are very
uncommonly needed, in my experience).

The point is that if you need to open a filtered report or form, you
use the base saved query and add a WHERE clause. DoCmd.OpenForms and
DoCmd.OpenReport can both take a WHERE clause argument that will
filter the result, so you don't need to embed anything in your saved
query.

And mostly, I don't use saved queries that much as plain old
recordsources. I only use them when I need nested queries to an
extent that is greater than what is allowed with subqueries of the
"[]. As Alias" form.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.

Relevant Pages

  • Re: JOIN on multiple conditions
    ... You must use the OUTER JOIN syntax, of course, ... >but if you learn only the INNER JOIN syntax does it change the way you ... More complicated queries should not be written by ... condition is placed in the ON or in the WHERE clause. ...
    (microsoft.public.sqlserver.programming)
  • Re: how to use partitions once created?
    ... Your example queries worked perfectly. ... is a Where clause and a slice the same thing - semantically ... > Please do not send email directly to this alias. ...
    (microsoft.public.sqlserver.olap)
  • Re: problem getting a query to recognize "Not" and "<>" in a value list combo box
    ... When I think of queries, ... over for a while before I can really grasp it conceptually. ... The WHERE clause is just a Boolean algebra expression which evaluates to ... if I want to explicitly retrieve all records. ...
    (microsoft.public.access.forms)
  • Re: OT: where to get mySQL questions answered?
    ... >queries for a search engine, I'm experiencing more crippling problems as ... >soon as I add an OR to my WHERE x LIKE '%blah%' type queries. ... >If more than one word is typed in, it adds to the WHERE clause. ...
    (comp.lang.php)
  • Re: where clause
    ... embedded in queries, I suspect that depends on the nature of the queries, ... A concept that may help to understand is  to note that Jet evaluates the ... Move the VBA function to the WHERE clause, and now, ... use another query as source if previous query to limit recrods. ...
    (microsoft.public.access.queries)