DLOOKUP being a PIA
in [Help]
|
From: The Frog on 30 Apr 2010 12:44 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 30 Apr 2010 19:38 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 30 Apr 2010 20:14 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 1 May 2010 06:22 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 1 May 2010 11:26
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? |