Combo Box to not display redundancies

[vha7]

Hi all,

I have a table that contains a list of different types of papers/articles. There is a column called "Category" that summarizes what that paper is about (i.e Safety, Review, Efficacy, Guideline,..., etc)
However, some rows/papers have more than one category type, separated by a comma or semi-colon, listed in the cell:

i.e
Safety
Safety, Efficacy
Review
Review, Efficacy, ..., etc

My goal is to have a combo box that only displays a dropdown of a single type of category (left combo drop down in image) without having the excessive cells that contain more than one term (right combo drop down in image) that retrieves the respective papers/results. Additionally, I want that one category term to be able to retrieve all cells that contain that category using a LIKE wildcard somehow (i.e if I click on the dropdown combo box and select "Safety" it should pull all papers that contain the word "Safety" in its category (i.e Safety; Safety, Efficacy; Safety, ..., etc), instead of ONLY retrieving papers that have "Safety" as its category.

The row source for the combo box attached on the left side is selected from a table that has column ID's labelled to its respective category term that I want.
The row source for the combo box attached on the right side is selected from the table that has a list of all the papers/articles.




Here is the code for my Category combo box:

Private Sub cmboCategory_AfterUpdate()
Dim myCat As String


myCat = "SELECT * FROM [Source1] WHERE ([Category] LIKE '" & Me.cmboCategory & "')" _
& "ORDER BY [Source1].[Year] DESC; "
Me.subPublications2.Form.RecordSource = myCat
Me.subPublications2.Form.Requery

applyFilt

End Sub

Attachment 34859

[pbaldy]

Perhaps

myCat = "SELECT * FROM [VDZ and NON TAK3] WHERE ([Category] Like '*" & Me.Combo7 & "'*)" _

[vha7]

Oops sorry, I have tried it with the LIKE wildcard but it doesn't work

[June7]

The second apostrophe is out of place. It must follow the *.

Like '*" & Me.Combo7 & "*')"

[vha7]

Oh wow, such a little mistake but that literally is what I was looking for. However, for example, it still displays:
Efficacy
Efficacy, Safety
Efficacy, Guideline
Efficacy, ..., ... etc
...underneath the combo box (right combo box in image). I want to only display the single term (as seen on the left combo box in the image) in the combo box.
How would I manage to do that? I am trying to manually delete it in the Row Source but I am unable to remove the excessive terms.

[June7]

How is the posted code relevant to this question?

Your attachment link does not work.

Perhaps you just need to change combobox Widths property, like: 0",1.0". Show combobox RowSource.

[vha7]

Just thought I'd let you guys know the code to the combo box in case if there's anything that could be added onto the code or fixes, etc. I am quite a beginner to Access and am learning through YouTube and Forums and doing what I can.

And the widths property does not alter anything. Is there a way to exclude the redundant dropdown terms through adding a criteria that limits the length of string so that it shows only the longest single word category term and those shorter than the limit? Just a hypothetical thought.

[June7]

I don't understand your data. Provide example of the data source for combobox.

[vha7]

So my main table is called "Articles" lets say. It contains, title, year, author, CATEGORY, etc etc.
I created a combo box, with a row source that pulls data from the source table: "Articles"; in which only the field of "CATEGORY" is displayed and selected.
Noting that there are x amount of entries that are considered each respective category, I created a subform for the table entries, and a form where I want to be able to choose from a combo box, of the Category of choice, to be able to display my specific results in the subform.

[pbaldy]

The second apostrophe is out of place. It must follow the *.

Like '*" & Me.Combo7 & "*')"

Stupid fat fingers.

[June7]

You still have not provide SQL for combobox RowSource nor example records. However, I might have a glimmer of understanding. If you want to show only the first data element of a CSV string, use string manipulation functions.

SELECT DISTINCT IIf(InStr([Category], ",")>0, Left([Category], InStr([Category], ",")-1), [Category]) AS Cat FROM Activities;

or

SELECT DISTINCT Left([Category], IIf(InStr([Category],",")>0, InStr([Category],",")-1, Len([Category]))) As Cat FROM Activities;


If you want EVERY word from ALL the strings and only one instance of each word, that is entirely different issue - and I do think this is what you are actually requesting.


If this field is not a CSV but actually a multi-value field, the multi-values can be expanded in a query. Review https://support.office.com/en-us/art...3-B6624E1E323A

If this data really is a CSV string, then will need VBA code that parses the string and writes records to a table and use that table as RowSource or appends items to listbox (RowSource type would have to be set to ValueList).

[vha7]

You still have not provide SQL for combobox RowSource nor example records. However, I might have a glimmer of understanding. If you want to show only the first data element of a CSV string, use string manipulation functions.

SELECT DISTINCT IIf(InStr([Category], ",")>0, Left([Category], InStr([Category], ",")-1), [Category]) AS Cat FROM Activities;

or

SELECT DISTINCT Left([Category], IIf(InStr([Category],",")>0, InStr([Category],",")-1, Len([Category]))) As Cat FROM Activities;


If you want EVERY word from ALL the strings and only one instance of each word, that is entirely different issue - and I do think this is what you are actually requesting.

Thanks, I will try these recommendations out - but your last point I think aligns with my thought process most.

If this field is not a CSV but actually a multi-value field, the multi-values can be expanded in a query. Review https://support.office.com/en-us/art...3-B6624E1E323A

If this data really is a CSV string, then will need VBA code that parses the string and writes records to a table and use that table as RowSource or appends items to listbox (RowSource type would have to be set to ValueList).

However this is exactly what I am dealing with - multi-values in the Category field. Wow, thanks, I will look more into this and try to figure something out.

[June7]

If the field is actually a multi-value field then how did those data get saved into that field? Wouldn't you already have a table of all these words to base a multi-select combobox or listbox on in order to save multiple values?

If the field does contain a CSV string (which is not the same thing as multi-value field) then as indicated, VBA will be required.

I never use multi-value field.

I presume this Category field is saving values that are the same type of entity (multiple categories in one field) and not a hierarchical description.

[vha7]

Sorry, should have confirmed that. The fields are CSV string and thus VBA is required.

You are correct that basically from the .xlsm file, the entries in the Category column are single, double, or multiple categories.