Combo box quandry

[Bhaughbb]

ok, so I felt like being slightly poetic..

On to the problem. I have a form that's fairly complex and drives a report our sales and management departments use to determine a lot of business decisions. The form has a couple of date fileds and 10 combo boxes. Each combo box get's its list from a query of various product/accounting tables so that all unique posibilities for each catagory to select from are listed. So far everything works wonderfully, if slow due to the number of queries and form options to check when the report is run.

My problem starts when one of our managers wanted to suddenly get results for all SKU numbers that start with a certain string. Basically he wants to do a wildcard criteria in a combo box, which of course doesn't work even with Limit to list set to no or false. This sku list has thousands of choices in it, any ideas on how to incorporate the ability to search with a wildcard (eg. "ADD*") or by the combo box listing in the same report?

 

[alastair69]

Have you tried the search option it would help you to find this, i know it is not exactly what you wanked but it is close.

Link : http://www.access-programmers.co.uk/forums/showthread.php?t=100512&highlight=postcodes

Alastair

 

[Bhaughbb]

Yeah, I spent about 2 hours searching and didn't find a relavent thread already in existance. I did not run across this one, all depends on what you search for.

As that thread suggested, Left() and Right() functions might allow me to put an iif statement in there to do what I need, as long as it passes on what is typed into the combo box.

 

[Bhaughbb]

OK, I've made some possible headway but it's still not working right. I confirmed that the wildcard (*) is being passed to the query from the combo box if it's typed in so I tried both of the following to allow for it in my results.

I tested that it would work by doing the following in the criteria:

Like [Forms]![Sales Dialog]![Combo94]

That worked so since I need to allow for null results I placed that in the existing criteria iif statment as:

IIf(IsNull([Forms]![Sales Dialog]![Combo94]),[Part_No],IIf(Right([Forms]![Sales Dialog]![Combo94],1)="*",Like [Forms]![Sales Dialog]![Combo94],[Forms]![Sales Dialog]![Combo94]))

I can still pull individual results but nothing returns the moment the wildcard is introduced. I'm sure I'm overlooking a simple syntax error somewhere but I'm not seeing it.