Solved Filtering a combo box (1 Viewer)

[Jediknight]

Hello,
Sorry for the noob question, but I’m trying to build a cascading combo box where the user gets to select a company (Company A, Company B, Company C). The main table contains the issues for each of the Company but it includes the sections as well which makes it complicated to work with... it is listed as such :
Company A-12 : Not enough supply
Company A-28 : Not enough money
Company B-13 : Not enough time
....
Etc

I’m able to do a query to pull out the data for Company A where I can see all the issues that Company A is having using a Like function.

In a form using a Combobox, how can I get to list Company A and Company B in that combo box? I don’t want to see Company A-12, Company A-28, Company B-13...etc.. in the combo box

Thank you for the help

 

[theDBguy]

Hi. Just a guess, but maybe you can try something like:

SELECT DISTINCT Left([CompanyFieldName], 9) AS Company FROM TableName

Hope that helps...

 

[Cronk]

if the length of the company name varies but always has the suffix C-nn, you could use
left([CompanyName], len([CompanyName]-4)

 

[Deleted member 147267]

Looks to me like you are basing the combo list on the wrong source, or the source is not properly normalized (or I'm just assuming too much). tblCompany should be providing the company list. If the list should only show companies with issues, it should be linked on tblCompany.CompanyPK to tblIssues.CompanyFK
Having to extract the company from what looks like a 2 field concatenation doesn't smell right.

 

[arnelgp]

or another approach:

SELECT DISTINCT Left([CompanyFieldName], Instr([CompanyFieldName], "-")-1) AS Company FROM TableName

 

[Jediknight]

There is not alot of company ... maybe 7 in total
Is there a work arround if I create a seperated table that contains just the list of the company? ... but then how would the cascading combo box be working ?

 

[Isaac]

You haven't answered the "if's" involving how reliable the pattern (of company name & section) is, but if there is some variation, then perhaps a reliable way would be to look for the hyphen and stop there. I am piggybacking off of dbGuy's suggestion with a tweak, so:

SELECT DISTINCT Left([CompanyFieldName], instr(1,[CompanyFieldName],"-")-1) AS Company FROM TableName

And I agree with Micron's comment about tables.
It seems to me sections should be in a separate table. Company would be in a companies table. Each company would be listed a single time with a primary key value. In the Sections table, there would be number like "12". If more than one company has a section "12", that's fine, then there would be multiple records with a value of "12" and a foreign key column designed to hold the primary key value from Company table, of the Company they belong to.
If, anyway, I am understanding correctly, and it sounds like a condo project I did once with Buildings and Units.

 

[Deleted member 147267]

On phone so I'll be brief for now. Re post 6 - it's not a work around to have 2 tables. Your current approach is the work around. Research cascading combos for part 2.

 

[Jediknight]

You haven't answered the "if's" involving how reliable the pattern (of company name & section) is, but if there is some variation, then perhaps a reliable way would be to look for the hyphen and stop there.

There are indeed some variations on how someone would write his Company nomenclature. There are 7 company. We have to assume that each company has a different name, sorry if I was miss leading in my initial post. One thing is for sure is that when an employee write its entry he/she writes in a way that the company name is there but ends it with the section sufix.
In another word one company name is Admin.
In the excell database the employee enters it like Admin36 and another employee in a different section would enter it Admin97
Another company would be Kitchen, but the employee would enter it like Kitchen63 and another one in that same company but in s different section would enter it Kitchen87.

 

[Isaac]

Wait .. so the first pattern you posted, that has a hyphen, and where you wanted to preserve the value prior to the hyphen - that's not even a valid scenario?

It sounds like you should maybe decide what the pattern actually is and then describe it clearly so that we can make relevant suggestions.

 

[Jediknight]

You are correct. When I laid the initial scenario I missed out the fact that with a select fonction we could isolate the company name, but unfortunatly it is not the case :/
Its a case of different company name (7 in total). All enteries are starting with one of the company name, that is for sure! Unfortunatly there is a suffix at the end that is added by the employee.

 

[Isaac]

Again, what are the possible pattern(s) that can exist for the suffix?
Like, "it's always alpha and then possibly a number, or a hyphen + a number, or a space + a number, at the end".
I just made that up as an example, but coming up with a defined statement about the possibilities is up to you.

 

[Jediknight]

Gotcha.
The suffix is a set of max 3 numbers from what I recall. No space, No hyphen, No special caracters.
Just like this : company123
...where my objective is for the combo box to show just company and disgard the suffix.

 

[Isaac]

Okay. I think you might have to write a VBA function that strips the numeric characters then from the string. Then reference it in your select statement for the combobox's rowsource. Let's say you write a function ReturnAlphaOnly(strInput as String) as String
Then I think you could use a select statement like:

Select Distinct ReturnAlphaOnly([companyfieldname]) from Table

The various functions listed here look pretty workable

Strip out non-numeric characters in SELECT

In an MS Access 2007 project report, I have the following (redacted) query: SELECT SomeCol FROM SomeTable The problem is, that SomeCol apparently contains some invisible characters. For example, I...

stackoverflow.com

 

[arnelgp]

you can also use Regular expression to remove the trailing numbers:

Function CleanString(strIn As String) As String
    'remove rightmost numbers
    With CreateObject("vbscript.regexp")
         .Global = True
         .Pattern = "(?:[^A-Za-z]+(\s*))*"
        CleanString = .Replace(strIn, vbNullString)
    End With
End Function

 

[Jediknight]

you can also use Regular expression to remove the trailing numbers:

Function CleanString(strIn As String) As String
    'remove rightmost numbers
    With CreateObject("vbscript.regexp")
         .Global = True
         .Pattern = "(?:[^A-Za-z]+(\s*))*"
        CleanString = .Replace(strIn, vbNullString)
    End With
End Function

Do I add this code in a Event procedure of the combobox ?

 

[arnelgp]

you add it to the RowSource of the combobox:

select CleanString([theCompanyFieldHere]) As Company From yourTable Group By

CleanString([theCompanyFieldHere]);​

 

[Deleted member 147267]

@arnelgp - I have to wonder what color your browser font is that you find it necessary to format parts of your posts as black text when browsers usually default to black text on white pages anyway. This is what formatted black text looks like in the dark theme. One can barely read such formatted code.

 

[arnelgp]

it is still in Black font on White background on my Old Edge (42.17134.1098).

 

[Deleted member 147267]

Well, I don't get what you're saying. You are not selecting the text and then formatting it as black using the AWF menu?
What I posted was a pic of your post using the AWF dark theme. If you don't understand what I'm saying, you might want to scroll to the bottom left of the page where the theme is shown, click/double click on that and change your default to shades of grey for ten minutes while you look at some of your posts. You won't be able to read a lot of code and comments you've posted. You are the only one that I can recall that has explicit black font in what they post.