Filter using Instr or Like?

[calvinle]

Hi,

I am declaring couple string in a constant:
Public Const BIGCOMPANY As String "Apple, Samsung, HTC"

And I wonder if anyone know I can apply this filter to my code, so in the combobox, if they choose BIGCOMPANY, it will filter thru the listing all record that has those company in the field [COMPANY]

I used to do for another method using InStr, but I cant figure out for the filtering..

If InStr(BIGCOMPANY, rs![COMPANY]) > 0 Then blabla

 

[pbaldy]

That should work. What does rs!Company have in it?

 

[calvinle]

Data such as Apple, Samsung or HTC.
I tried to do the filter but it doesn't work by calling the filter using "Like".

 

[pbaldy]

Oh, I was thinking you were trying to get that code to work. You're setting a filter? Perhaps:

Me.Filter = "COMPANY In(" & BIGCOMPANY & ")"

though the values would likely need single quotes around them.

 

[gemma-the-husky]

I would add a "bigcompany" flag to the customer table. Then just filter for bigcompany = true

 

[pbaldy]

That's actually what I would do too. Puts control of the items in user hands.

 

[calvinle]

Oh, I was thinking you were trying to get that code to work. You're setting a filter? Perhaps:

Me.Filter = "COMPANY In(" & BIGCOMPANY & ")"

though the values would likely need single quotes around them.

After update the combobox, it asks to enter the Value Parameter??

I would add a "bigcompany" flag to the customer table. Then just filter for bigcompany = true

What do you mean??

 

[pbaldy]

The parameter prompt is normally Access telling you it can't find something. Dave's suggestion involves adding a yes/no field to your company table. Checked means that company is a "big company". Then your filter is:

"BigCompany = True"

One advantage is that when you get new customer that qualifies, you just check that box in the customer data. No program changes are required (adding it to your constant).

 

[calvinle]

Get it..!
But it will not solve my other issue. I have another combobox which has 2 value: Canada or USA. I will need to filter those base on the BIGCOMPANY. If the USA is selected in the combobox, then it will filter to only show Apple, Samsung and HTC which are my BIGCOMPANY associated.

 

[pbaldy]

How are you handling it now? You can apply the filter if USA is chosen and not if Canada is chosen.

 

[Galaxiom]

Expanding on what Dave (gemma-the-husky) said.

By including the names of companies into the code you are storing data in the code and that is a serious mistake. Code is meant for processing data, not storing it.

As a general principle, if changing some aspect of the data requires changing code, redesigning tables, queries, forms or reports then the data is not stored correctly. Any data change should be achievable using runtime facilities only.

All the information about the companies should be stored in tables.

 

[calvinle]

Thanks, that make sense.. But I have more knowledge on vba than access vba, so that's why I used the old way to enumerate data thru vba.

As per my post above, I stated that I associate a range of company in my vba code and I used, Instr..to detect if the record has the company name.
If I have to put them in a table, lets say tblCompany, then in field [BIGCOMPANY].
How can I rename my Instr code to the rs![COMPANY]?
Using Dlookup?