ComboBox Wildcard problem (1 Viewer)

Minty

AWF VIP
Local time
Today, 06:05
Joined
Jul 26, 2013
Messages
10,355
Hi All,

I'm trying to make a filtered search form using "*" as a wildcard default value in comboboxes, this works for all the text fields except for the account number field (Numeric primary key).

After quite a bit of reading up and searching, I tried using the following as the row source;

SELECT customers.ACCOUNT_NO, customers.CUSTOMER FROM customers; UNION SELECT "*", "All" From Customers;

But am still getting "#Error" in the combo box.

What am I missing? I have as usual been going round in circles trying different things to no avail...
 

Brianwarnock

Retired
Local time
Today, 06:05
Joined
Jun 2, 2003
Messages
12,701
Presumably the error occurs because "*" is not numeric.

I guess that you are code ding a query with a Where clause such as

Where fielda like forms!serachform!combo1
And fieldB like forms!searchform!combo2
And etc

I never use Like unless I am doing partial string searches which of course you are not when selecting from a combo. I have read that the use of Like means that indexes cannot be used which will result in a less efficient search, although I must add that a senior member on here has said that he thinks they can when no wild cards precede the string.

I use the following approach

Where (fielda= forms!searchform!combo1 OR forms!searchform!combo1 Is Null)
And (fieldB =forms!search!combo2 OR forms!searchform!comb2 Is Null)
And etc

Save the query in SQL view only as the QBE view goes crazy if you shaven it from there, you can view it but don't save it.

This is simple and effect

You could of course just use this technique on your numeric field.

Brian
 

Minty

AWF VIP
Local time
Today, 06:05
Joined
Jul 26, 2013
Messages
10,355
Thank you - that cured the problem.
I used the is null Or = ... route as it made more sense in this particular instance.
 

Users who are viewing this thread

Top Bottom