Using wildcard as choice in combo box 1

[Straw]

I am using an unbound form to print specific information in a report. Each item is a dropdown list which is based on a table. However if a user does not wants to see all the items for a particular category then I want them to be able to use the asterick to do this which is set as the default value for each dropdown menu.

Currently this is done by :
A macro runs when button is pushed on form after.
Macro says OpenForm and the Where item is set to [item1]=[Forms]![DataCrit]![item1] And [item2]=[Forms]![DataCrit]![item2]......etc

Now this works if they select an item, but if they do not and the asterick remains then I get an empty record set. How can I get an option in each dropdown menu so that they can set it to All or use an asterick so it selects all the data for that particular criteria?

 

[tlbroadbent]

Try something like this.

Where ([item1]=[Forms]![DataCrit]![item1]
Or [Forms]![DataCrit]![item1]="*&quot
And ([item2]=[Forms]![DataCrit]![item2]
Or [Forms]![DataCrit]![item2]="*&quot ... Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Steve101]

... or another approach:

(a) Use the LIKE operator in your selection criteria; for example:

[item1]LIKE [Forms]![DataCrit]![item1] And
[item2]LIKE [Forms]![DataCrit]![item2]......

(b) Set the 'limit to list' property on your dropdown lists to NO

(c) You can then select items from your dropdown list, for an exact match, or prefix, suffix or replace the list item value on the form with the * and/or % wildcard characters, to get the best of both worlds re exact or pattern matching search.

Have fun,
Steve

 

[Straw]

OK, thanks for the input. The

Where ([item1]=[Forms]![DataCrit]![item1]
Or [Forms]![DataCrit]![item1]="*&quot
And ([item2]=[Forms]![DataCrit]![item2]
Or [Forms]![DataCrit]![item2]="*&quot ...

seemed to address the wildcard issue. However, I was trying to use this as a "Where" statement for the OpenReport action in a macro. However, my "Where" statement is longer then the allowed length. Therefore, how do I accomplish this same thing? I tried to save the "Where" statement as a query and then put it in the "Filter Name" for the OpenReport action in the macro, However it did not seem to work. How do I accomplish this with such a long Where statement? Am I going about it the wrong way?


Thanks!

 

[Spyder757]

Ok guys this is exactly what I'm looking for.

I'm a little lost though. Would this:

Where ([item1]=[Forms]![DataCrit]![item1]
Or [Forms]![DataCrit]![item1]="*&quot
And ([item2]=[Forms]![DataCrit]![item2]
Or [Forms]![DataCrit]![item2]="*&quot

Go in the query, or a macro? I'm still learning and any help would be most welcome.

Mark

 

[tlbroadbent]

That is the WHERE clause of a QUERY. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[danvlas]

Maybe:
WHERE IIf([Forms]![DataCrit]![item1] ="*", ([item1] Is Null) Or ([item1] Is Not Null), [item1] = [Forms]![DataCrit]![item1] )

will do...

Regards,

Dan