 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / December 2005[urgent] Filter based on lookup combo
Hi! I want to build a SQL statement according to a "filter by form" selection of the user. So what I do is to capture the "ApplyFilter" event and use the Filter property as a WHERE-clause for a query. Unfortunately some fields are lookup values, even compound ones. So my filter looks like <Expr_1001="searchme">. Of course the query (outside the form) does not know this expression. Can I make Access build its filter conditions not based on the looked-up expression but on the underlying numerical value? Thank you for your ideas. Best regards, Andreas I can't tell for sure, but it sounds like you have the fields in your tables set to be lookup values. This is something to avoid, because it causes problems just like this. It's better to just make your fields be ID values with no lookups and use comboboxes in forms do the looking up from separate lookup tables. Then you can get at the actual numeric Id's. For more info, try searching google groups on "dont use lookup fields" Here's one good thread: http://groups.google.com/group/microsoft.public.access.gettingstarted/browse_thr ead/thread/2d3c1b122063deb5/93f4e54e5ab24e00?lnk=st&q=dont+use+lookup+fields&rnu m=3#93f4e54e5ab24e00 hope this helps -John > Hi! > [quoted text clipped - 12 lines] > Best regards, > Andreas > I can't tell for sure, but it sounds like you have the fields in your tables > set to be lookup values. This is something to avoid, because it causes > problems just like this. Hi, John, thanks for the hint. It helped me to narrow down the problem. The field itself is a numerical value, but the lookup actually was defined bith in the table AND in the ComboBox. So I removed the lookup definition from the table, but it did not solve the problem. But I found out something else. My ComboBox lookup statement looks like this: SELECT [TableA].[ID], [TableA].[FirstName] + " " + [TableA].[LastName] FROM TableA. So I have to columns. To hide the ID, I set the column witdh of the first column to 0. And this is what causes the filter to yield "Expr_1001=<seachme>". If I set the column width of the first column to anything but 0, the filter yields the correct "ID=1". Is there a solution for this? Still 10 hours of night work till deadline :-) Best regards, Andreas Well, you could set the first column width to .01 and it still won't show when you drop down your box, but then you will see the number in the combobox after you make the selection. Probably not what you want, though, eh? >> I can't tell for sure, but it sounds like you have the fields in your >> tables [quoted text clipped - 28 lines] > Best regards, > Andreas Unless you really need the ID, go back to the Query and simply drop the ID from the returned fields. This also means changing the combo box as to bound field and how many fields and the widths of them. The wizard always wants to add ID even when unneeded or in some cases cannot have. (Trying to get a "distinct" query cannot have ID, for instance.) Another way is to change the sequence of the fields in the query, this also will require changing the bound field and the fields lengths. Ron Can't you run the subqueries in code and use the replace function to replace the searchme strings in the WHERE string until you have a clean WHERE clause? > Hi! > [quoted text clipped - 12 lines] > Best regards, > Andreas |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.