From: The Frog on
Hi Salad,

Yes and no. Yes it is what is currently saved, but no it doesnt work.
It is also not what I want to work - I just cant save the query unless
it is typed this way as all other (seemingly) combinations result in
the String error and you cannot save the query with that error
present.

What I tried in place of what is saved is as above. The criteria
condition for the DLookup should read: "[category_id] = " &
[lstCategory] . If I use this I get the error. If I do the same with
single quotation marks around the [lstCategory] value then I get an
error. If I remove all spaces after the = sign I get the same error.
It doesnt seem to matter where or how I try and build the criteria I
keep getting the 'String' error. I only kept it saved as above so that
I would not lose my place so to speak. In the Access 2000 Bible it
states to remove all the spaces after the equals sign when typing in
your description of the criteria. I have done this and it still doesnt
work. eg/ "[category_id]='"&[lstCategory]&"'" . No luck there with
or without a space between the [category_id] and the = sign.

In short anything that should reasonably work doesnt seem to and will
produce an error. The only thing that doessnt produce an error is a
badly formed criteria that wont function.

For the timebeing I have changed the DLookup to a SELECT DISTINCT
statement that gets the job done. Nested SQL is starting to get to
what I consider an acceptable limit when it hits the 3rd level. I
prefer to only have two levels deep if I can - else I find things
start to run slow, especially if you are using correlated subqueries
(but we all know they are slow anyway...).

I am stumped as to what is going on with the query syntax here. The
DLookup works fine outside of the query, but I just dont seem to be
able to set it correctly in a criteria.

Cheers

The Frog
From: Salad on
The Frog wrote:

> Hi Salad,
>
> Yes and no. Yes it is what is currently saved, but no it doesnt work.
> It is also not what I want to work - I just cant save the query unless
> it is typed this way as all other (seemingly) combinations result in
> the String error and you cannot save the query with that error
> present.
>
> What I tried in place of what is saved is as above. The criteria
> condition for the DLookup should read: "[category_id] = " &
> [lstCategory] . If I use this I get the error. If I do the same with
> single quotation marks around the [lstCategory] value then I get an
> error. If I remove all spaces after the = sign I get the same error.
> It doesnt seem to matter where or how I try and build the criteria I
> keep getting the 'String' error. I only kept it saved as above so that
> I would not lose my place so to speak. In the Access 2000 Bible it
> states to remove all the spaces after the equals sign when typing in
> your description of the criteria. I have done this and it still doesnt
> work. eg/ "[category_id]='"&[lstCategory]&"'" . No luck there with
> or without a space between the [category_id] and the = sign.
>
> In short anything that should reasonably work doesnt seem to and will
> produce an error. The only thing that doessnt produce an error is a
> badly formed criteria that wont function.
>
> For the timebeing I have changed the DLookup to a SELECT DISTINCT
> statement that gets the job done. Nested SQL is starting to get to
> what I consider an acceptable limit when it hits the 3rd level. I
> prefer to only have two levels deep if I can - else I find things
> start to run slow, especially if you are using correlated subqueries
> (but we all know they are slow anyway...).
>
> I am stumped as to what is going on with the query syntax here. The
> DLookup works fine outside of the query, but I just dont seem to be
> able to set it correctly in a criteria.
>
> Cheers
>
> The Frog

Hi Frog:

I tend to stay away from sub selects. I had performance issues with
them years ago so I haven't used them since.

The bottom line, if I'm reading your post correctly, is that you need to
use incorrect syntax to make it save. But using correct syntax causes
problems.

It's quite possible it is a bug in the Access software. Perhaps make a
bug report to MS. I think that by modifying the code to something that
works as you did is your best bet. Beating the head over something you
don't have control over simply leads to frustration and doesn't
accomplish much. Take what works and move on to the next problem.

From: David W. Fenton on
Salad <salad(a)oilandvinegar.com> wrote in
news:4qWdnbfNUbYDBUDWnZ2dnUVZ_gWdnZ2d(a)earthlink.com:

> I tend to stay away from sub selects. I had performance issues
> with them years ago so I haven't used them since.

There are a whole bunch of different types of subqueries. The most
performance-troublesome are correlated subqueries, but it should be
obvious why that's the case (the subquery has to execute for each
row of the main query).

A subselect of this form:

SELECT *
FROM (subselect}

....is really no different from using a saved QueryDef in the
subselect. The reason one would use a subselect instead of a saved
QueryDef is if your WHERE clause varies. Performance should be only
marginally different with the same WHERE clause in the subselect and
the saved QueryDef, the only difference being whatever benefit there
is from the compiled optimization of the saved QueryDef.

I see no reason to be "afraid" of subselects at all. Sometimes, they
are the only way to get the job done.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
From: The Frog on
Hi Guys,

In the end I solved it with the following nested subquery:

Not In (SELECT raw_value FROM dictSubcategory WHERE parent_id =
(SELECT DISTINCT dictionary_id FROM dictCategory WHERE category_id =
[lstCategory]) )

Its a little sluggish, but not too bad. I dont know how it will
perform over a network. Would it help if I took the criteria subquery
above and broke it into two separate queries and saved them as such?
Then refer to the 'outer' query of the two for the criteria? Or will
Access (2003) optimise the query just the same leaving the raw SQL in
the criteria field?

I am still buggered by the DLookup thing. I am going to come back to
this at a later stage because I just want to know why it doesnt behave
itself - it should! Damned if I know what it could be.

Cheers

The Frog
From: David W. Fenton on
The Frog <mr.frog.to.you(a)googlemail.com> wrote in
news:c6c2d766-e7f7-40a9-9d64-22d4cd0a2b95(a)a34g2000yqn.googlegroups.co
m:

> In the end I solved it with the following nested subquery:
>
> Not In (SELECT raw_value FROM dictSubcategory WHERE parent_id =
> (SELECT DISTINCT dictionary_id FROM dictCategory WHERE category_id
> = [lstCategory]) )

Why is the subselect in the IN clause not done with a JOIN?

> Its a little sluggish, but not too bad. I dont know how it will
> perform over a network. Would it help if I took the criteria
> subquery above and broke it into two separate queries and saved
> them as such?

It would likely make no difference, but it's hard to tell from
SHOWPLAN, since MS never implemented explaining optimization of
subqueries.

> Then refer to the 'outer' query of the two for the criteria? Or
> will Access (2003) optimise the query just the same leaving the
> raw SQL in the criteria field?

I worry that the subselect in the inner WHERE clause won't get
optimized to use the index on both sides. If you wrote that IN
clause as a single SQL statement with a JOIN, I suspect it would be
more likely to use both indexes.

> I am still buggered by the DLookup thing. I am going to come back
> to this at a later stage because I just want to know why it doesnt
> behave itself - it should! Damned if I know what it could be.

The discussion earlier in the thread was way to voluminous for me to
follow closely, but are you in need of an editable resultset? If
not, you may not need the subselects at all.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Need help in list box
Next: A2007: Overlapping or tabbed