DLOOKUP being a PIA
in [Help]
|
From: The Frog on 2 May 2010 10:08 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 2 May 2010 11:22 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 2 May 2010 16:48 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 3 May 2010 03:42 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 3 May 2010 15:40 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 |