From: The Frog on
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
From: Bob Quintal on
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(a)googlemail.com> wrote in
news:e97d0cbf-a051-424b-a79b-af35dba0e287(a)q32g2000yqb.googlegroups.co
m:

> 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]='"&[lstCatego
> ry]&"'"))
>
> 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

From: Salad on
The Frog wrote:
> 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

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)))
From: The Frog on
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
From: Salad on
The Frog wrote:
> 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

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