Solved Filtering a combo box (1 Viewer)

Jediknight

New member
Local time
Today, 13:21
Joined
Jul 11, 2019
Messages
15
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

I’m here to help
Staff member
Local time
Today, 10:21
Joined
Oct 29, 2018
Messages
21,358
Hi. Just a guess, but maybe you can try something like:
SQL:
SELECT DISTINCT Left([CompanyFieldName], 9) AS Company FROM TableName
Hope that helps...
 

Cronk

Registered User.
Local time
Tomorrow, 04:21
Joined
Jul 4, 2013
Messages
2,770
if the length of the company name varies but always has the suffix C-nn, you could use
left([CompanyName], len([CompanyName]-4)
 

Micron

AWF VIP
Local time
Today, 13:21
Joined
Oct 20, 2018
Messages
3,476
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:21
Joined
May 7, 2009
Messages
19,169
or another approach:

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

Jediknight

New member
Local time
Today, 13:21
Joined
Jul 11, 2019
Messages
15
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 ?
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 10:21
Joined
Mar 14, 2017
Messages
8,738
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:

Code:
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.
 

Micron

AWF VIP
Local time
Today, 13:21
Joined
Oct 20, 2018
Messages
3,476
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

New member
Local time
Today, 13:21
Joined
Jul 11, 2019
Messages
15
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

Lifelong Learner
Local time
Today, 10:21
Joined
Mar 14, 2017
Messages
8,738
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

New member
Local time
Today, 13:21
Joined
Jul 11, 2019
Messages
15
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

Lifelong Learner
Local time
Today, 10:21
Joined
Mar 14, 2017
Messages
8,738
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

New member
Local time
Today, 13:21
Joined
Jul 11, 2019
Messages
15
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

Lifelong Learner
Local time
Today, 10:21
Joined
Mar 14, 2017
Messages
8,738
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:
Code:
Select Distinct ReturnAlphaOnly([companyfieldname]) from Table
Code:

The various functions listed here look pretty workable
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:21
Joined
May 7, 2009
Messages
19,169
you can also use Regular expression to remove the trailing numbers:
Code:
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

New member
Local time
Today, 13:21
Joined
Jul 11, 2019
Messages
15
you can also use Regular expression to remove the trailing numbers:
Code:
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 ?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:21
Joined
May 7, 2009
Messages
19,169
you add it to the RowSource of the combobox:

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

Micron

AWF VIP
Local time
Today, 13:21
Joined
Oct 20, 2018
Messages
3,476
@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.

blackText.jpg
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:21
Joined
May 7, 2009
Messages
19,169
it is still in Black font on White background on my Old Edge (42.17134.1098).
 

Micron

AWF VIP
Local time
Today, 13:21
Joined
Oct 20, 2018
Messages
3,476
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.
 

Users who are viewing this thread

Top Bottom