Hi Everyone,

I am using a query to populate a listbox on a form. There is a
criteria for this query that is based on the value of another listbox.
The tables behind are normalised and the 'heirarchy' of relationships
between the tables is reflected in the heirarchy in usage of the
listboxes.

The 'parent' listbox is called lstCategory, and has a return value of
Long (its an autonumber value that is being returned)

The 'child' listbox is called lstSubCategory and has a query as its
datasource. In this query is a criteria. This criteria is itself a
query with a WHERE clause based on a DLookup. The criteria is as
follows:

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

In theory this should work. If I use this expression, then I am
receiving incorrect results as the 'Not In' does not appear to be
producing the correct list of values. If I do the 'Not In' part by
hand (ie/ manually see what the results should be with SQL) then I am
receiving a list of values to exclude that is correct.

I am under the assumption that my DLookup statement is wrong
somewhere, but when I try and alter the statement in any way I am
receiving Invalid String error messages. For example I have tried the
following based on the Access 2000 Bible (p770) instructions:

Not In(SELECT raw_value FROM dictSubCategory WHERE parent_id =
DLookup("dictionary_id","dictCategory","[category_id]='"&[lstCategory]&"'"))

I have also tried it without the single quotes as it is a numeric
value that is being passed along but I receive the same error message
about strings. I have the correct number of open and close quotes but
I am dammned if I can figure this out. Effectively what my criteria
should be is:
[category_id] = [lstCategory]

Cant seem to get it to function. Can anyone help me with this one? It
is so seemingly simple and its driving me insane. I am at a loss and
would appreciate anyones guidance.

Cheers and Thanks

The >Frog

The first things I see is a misplaced quote and a missing ampersand
in the DLookup's WHERE Clause:
"[category_id]" = [lstCategory]
should be
"[category_id] = " & [lstCategory]

But then I realize you also need to reference the form name for
LstCategory in the query:

"[category_id] = " & Forms!frmName!lstCategory

HTH

Bob



The Frog <mr.frog.to.you (AT) googlemail (DOT) com> wrote in
news:e97d0cbf-a051-424b-a79b-af35dba0e287 (AT) q32g2000yqb (DOT) googlegroups.co
m:

The Frog wrote:
Is this real code or copy/pasted/modified code you posted? In the first
clause you used
"[Category_id]" = [lstCategory]...
That looks incorrect with that = sign.

What happens if you used
"[Category_id] = " & Forms!MyFormName!lstCategory)))

Hi Guys,

Thanks for the feedback. I have tried this thinking that it was the
right thing to do but I am still getting the 'string' error when
trying to save the query. This is what I have following the above
advice that produces the same error:

Not In (SELECT raw_value FROM dictSubcategory WHERE parent_id =
DLookup ("dictionary_id", "dictCategory","[category_id] = " & Forms!
[frmDictionarySubCategory]![lstCategory] ) )

If I use the VBA Immediate window while the form is running and test
the DLookup I am getting the right data back, it just doesnt seem to
want to work inside of a queries criteria. I was thinking of swapping
the DLookup out with another nested SELECT statement and trying to
ensure that I get only a single response back. It seems a shame as the
DLookup is perfect for this situation.

The full SQL statement is as follows:

SELECT DISTINCT raw_OSA.SubCategory
FROM raw_OSA
WHERE (((raw_OSA.SubCategory) Not In (SELECT raw_value FROM
dictSubcategory WHERE parent_id = DLookup ("dictionary_id",
"dictCategory","[category_id]" = [lstCategory] ) )) AND
((raw_OSA.Category) In (SELECT raw_value FROM dictCategory WHERE
category_id = [lstCategory])))
ORDER BY raw_OSA.SubCategory;

Please note that this 'full' SQL has the non-functional DLookup in it.
The only way I was able to save the query is with this non-functional
form hence the silly locations for the quotation marks - I just didnt
want to lose my thinking on the way through developing this app so I
saved it as I could.

The table structure is that there are four tables, category,
subcategory, dictCategory, dictSubcategory. Subcategory is linked to
Category via the category_id field (fk in Subcategory and pk in
Category). DictCategory is linked to Category in the same way.
DictSubcategory is linked to both subcategory and dictCategory.
DictSubcategory to SubCategory on subcategory_id, and to dictCategory
on parent_id (fk) which is the dictionary_id field in dictCategory. I
hope that makes sense.

Cheers

The Frog

The Frog wrote:
Again, is this line in your post
"dictCategory","[category_id]" = [lstCategory] ) )) AND
a mistake in trascribing or actual?

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

The Frog wrote:

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.

Salad <salad (AT) oilandvinegar (DOT) com> wrote in
news:4qWdnbfNUbYDBUDWnZ2dnUVZ_gWdnZ2d (AT) earthlink (DOT) com:

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/

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

The Frog <mr.frog.to.you (AT) googlemail (DOT) com> wrote in
news:c6c2d766-e7f7-40a9-9d64-22d4cd0a2b95 (AT) a34g2000yqn (DOT) googlegroups.co
m:

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

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

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.

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/