[urgent] Filter based on lookup combo

[hufflehuffle]

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

 

[John Welch]

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/micr...ont+use+lookup+fields&rnum=3#93f4e54e5ab24e00
hope this helps
-John

 

[Guest]

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?

 

[hufflehuffle]

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

 

[John Welch]

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
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

 

[Ron2005]

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