I tried to append '*' to the front and rear of each keyword in my SQL query that generates the list as follows:

<CODE>
SELECT '*' & tKeywords.keyword & '*' FROM tKeywords GROUP BY tKeywords.keyword ORDER BY tKeywords.keyword;
</CODE>

It does add the * but the filter may be ignoring it or something because that didn't work.

Jasen,

I have checked out the code at http://allenbrowne.com/func-concat.html.
Very interesting. I will have to try it myself. For now, try the following:

Use double quotes instead of single quotes.
Put your select statement in a variable and msgbox it to make sure it is correct.

If I have time, I will test it.

Find Record

here is the video for Duble Duble Quotes
Double Double Quotes

Sami - perhaps I didn't explain well enough.... Let me try again.

I used the multiple combo box filter from Richard's video (linked in my first post) to allow my users to FILTER the form results. I have three fields to filter by: CLIENT, CONTRACT, KEYWORD. His VBA code makes use of the FILTER functions in Access, not SQL. The only place I (and he) uses SQL is to generate the combo box values.

CLIENT and CONTRACT are easy - simple one option to select and filter based on since all records will only have ONE client and ONE CONTRACT. This works fine.

KEYWORD is more difficult because in the form's output itself, I have concatenated the keyword values. So they get listed as:  apple, banana, orange  OR pineapple, grape ETC. This is all based on how the entries are created and tagged. This also works and looks great.

In the FILTER combo boxes, I dynamically generate them based on other tables in the database so people can only select things in the database when they search. So for my keyword combobox I use the SQL:

SELECT "*" & tKeywords.keyword & "*" FROM tKeywords GROUP BY tKeywords.keyword ORDER BY tKeywords.keyword;

My ComboBox filter for keywords now shows:

*apple*
*pineapple*
*orange*
*grape*
*banana*

I figured that it would pass *apple* to the VBA code that's filtering the records and make use of the wildcard feature so that if a record had: apple, banana in the keyword box it would keep it on screen. Instead it's just filtering all of them away.

There is no MSGBOX to use and no SQL to post from the filtering because there is no SQL being used to filter - just the Me.Filter function from the code Richard uses.

Screenshots?

Ok first things first:
The wildcard character "*" will only work in a "LIKE" for example:

"SELECT Keyword FROM tKeywords  WHERE Keyword LIKE "*apple*"
or
"SELECT * FROM tKeywords  WHERE Keyword LIKE " & """*""" & Keyword & """*""""

I'm thinking you're not seeing the records you expect because none of the records have the "*". They are only in the Combo Box. Is there a reason why you need them? This is how I would write the SQL for the Row Source for the Combo Box:

SELECT DISTINCT keyword FROM tKeywords ORDER BY keyword

If you look at the Keywords in the screen shot, they are shown as

SSN, UUV, SSN(X)

This is the function of a concatenation I did of all the keywords to create the clean look of them in a single line of text instead of having the stupid stacked subform of them that makes it look ugly.

The combo box generates the list of all possible keywords in the database. The user needs to be able to select one and the records filter out any that do not have the keyword in the concatenated list. The idea was, since the VBCode is using a filter function and not an SQL query, that the * would add a wildcard to the FILTER function. It's not. When the star is removed, the filter by keyword doesn't work either because that box with the keywords doesn't exactly match the combobox values (since there is a concatenated string of keywords in it).

Hi Jasen, maybe this could be fixed simply by changing "AND" to "OR" in your AddToFilter procedure.  AND is actually more restrictive than OR, and that may be why you are missing items.  In real life, if you order "coffee and tea" you get more drinks than you would if you said "coffee or tea."  But in logical lingo, it's the reverse: AND actually (almost always) gives you fewer items than OR does.

Kevin, even when ONLY something is set in the KEYWORD box - it still filters everything out.

You need to make sure Me.Filter gets a valid expression.  Find out what the value of TempVars("Filter") at the moment the filter is set.  Are you familiar with basic debugging techniques?  Put a break point on the line Me.Filter = TempVars("Filter").  Open the form, change the combo box's value, and the execution will pause on that line and highlight that line in yellow.  Then type this in the immediate window:

     ?TempVars("Filter")

Press Enter, and see if the value is as expected.  If the filter is invalid, it may evaluate to false, and thus no record will show.

Setting the Break Point and only setting the Keywords it returns:

?TempVars("Filter")
keywords="*MK-48*"

Setting two different variables returns:

clientName="OPNAV" AND keywords="*MK-48*"

Looks like its returning things as expected, but the * isn't acting like the wildcard.

Use the "Like" operator to match a pattern.  The equal operator "=" requires exact matches:

     keywords Like "*MK-48*"

Again, check if you should use OR instead of AND because AND is more restrictive and could be another reason why you are missing records.

I notice you have similar keywords that wildcard filters may not help you find.  For instance, Like "*SSN*" will return both "SSN" and "SSN(X)".  The only way to return only "SSN" is to find exact matches.  You need to split your comma-delimited list of keywords into individual keywords (with the Split() function, for instance), then find exact matches of them.

I have managed to make this code work and achieve results:

<CODE>Private Sub AddToFilter(FilterComboName As String)

    If IsNull(Me(FilterComboName)) Then Exit Sub
    If TempVars("Filter") <> "" Then TempVars("Filter") = TempVars("Filter") & " AND "
    TempVars("Filter") = TempVars("Filter") & _
        Left(FilterComboName, Len(FilterComboName) - 6) & " LIKE """ & Me(FilterComboName) & """"
    
End Sub</CODE>


Changing the "=""" to " LIKE """ makes all the combo boxes act as LIKE searches but since the others don't have the * it seems to force an exact match.

Kevin, for the split recommendation... can you elaborate? Right now each PROJECT record links to a KEYWORD table with a PROJECTxKEYWORD linking table (MANY to MANY relationship reasons). I used the concat code from allen browne to make my output cleaner which is how I get the CSV list of keywords.

Allen Browne's method is mainly for display purposes.  A comma-separated list looks nice for display, but it is not useful for constructing criteria for queries.  You have to find a way to break up the keywords again (undoing what Allen Browne did) and look them up one by one for exact matches.  Wildcards are for "fuzzy" searches, and I don't think that is your keywords' purpose.  You want your keywords to match exactly, not fuzzy-match, the records you want to find, especially when you have keywords that look alike.  With fuzzy searches, you risk inadvertently matching something you don't want to match.

Since you use Allen's Browne's method to concatenate, your original data must be UNconcatenated, separate, and normalized records.  I suggest you create a custom function your own that uses a recordset loop to look through all keywords that belong to each record, and find matches.  The function then returns true or false to indicate found or not.  Your query can then use your function, and it will look something like:

     SELECT * FROM Table1 WHERE HasKeyword("SSN") = True AND HasKeyword("UUV") = True

Kevin, good thoughts about the display vs. fuzzy nature of the search. Unfortunately, until I figure out a clean way to output data horizontally so it looks good (UX is just as important as functionality) - I may be stuck with doing it the way I did.

You can keep Browne's function for display purposes.  But to search without using fuzzy matches, you need to create a custom function of your own.  Both Browne's function and your function can coexist; you don't need to give up one or the other.  You simply add functionality to it.  And the functionality you need to add is a way to find exact matches of your keywords.  And there is no way to do that with comma-delimited strings.  For instance, if you have records with strings that look like:

     armchair, chair, hair, air, lair
     chair, hair, lair
     armchair, hair, air, lair

Then there is no way to correctly find where "chair" is with your method.  If you search for *chair*, you will get all three records even though "chair" doesn't exist in the 3rd record.  That's because *chair* will also match "armchair" in the third record.  

Similarly, there is no way to correctly find "air", because "air" is a substring of all the other keywords.  "air" only exists in the 1st and 3rd record above, but searching for *air* won't find that.  This is why wildcard matches don't work for your purpose, because one keyword may be a substring of another.  You need exact matches for this.

I understand what you're trying to say... however, if I use a sub form to publish the keywords in a vertical list (since that's the dumb way access will always do it) - it's going to throw of the entire look unless I "hide" the sub form (which I am not sure I can do). Or I am not understanding the final recommendation here? Unlike Richards example in the YT video, the search isn't applying to the single table, they keywords are in another table connected to the first using a junction table. - I've brute forced this kind of thing in the past using PHP and MySQL - access is annoying.

You don't need to change to look of your form.  You don't actually have to add a subform to have access to keywords.  You can access them "internally."  As I said, a custom function should help you here, by way of opening a recordset.  A recordset can deal with multiple tables, so that solves that problem for you.  A multi-table setup like yours is fairly routine in relational database, and so does using custom functions.  Access has VBA that lets you write custom functions, and SQL Server lets you write "stored procedures" and the likes.  So this is fairly routine practice in most database systems.  After you master this kind of thing, you won't think it's annoying.

Make a custom function called "HasKeyword" that opens up multiple tables related to your current project, from which it checks if the project in question contains the keyword you want to find.  The function may look like:


Public Function HasKeyword(idProjects As Long, KeywordToFind As String) As Boolean
    Dim r As Recordset, b As Boolean
    b = False
    Set r = CurrentDb.OpenRecordset("SELECT keyword FROM tKeywords INNER JOIN tProjectsXKeywords ON (tKeywords.idKeywords = tProjectsXKeywords.idKeywords) WHERE tProjectsXKeywords.idProjects = " & idProjects)
    If r.RecordCount > 0 Then
        r.MoveFirst
        Do Until r.EOF
            If r!Keyword = KeywordToFind Then b = True: Exit Do
            r.MoveNext
        Loop
    End If
    r.Close
    HasKeyword = b
End Function


The function above assumes all your ID fields are long integers.  The key line is OpenRecordset(...), which creates a multi-table set of data that joins your project to all the keywords of that project.  Then a "recordset loop" is used to go through all the keywords one by one, until it finds an exact match, or not.  The function then returns true or false.

When the user chooses a keyword (with no asterisks) from the combo box, you can then filter the form either by changing the form's record source to:

     SELECT * FROM MyTable WHERE HasKeyword(idProjects, ComboBoxValue) = True

or (preferably) set the form's filter in VBA:

     Me.FilterOn = True
     Me.Filter = "HasKeyword(" & Me.idProjects & ", '" & Me.ComboBoxValue & "') = True"

If you are not familiar with custom functions, recordsets, recordset loops, etc., then you need to learn them.  Other posters will be able to suggest what courses to see here.  I don't have access to Richard's courses, unfortunately.

VBA Loops
Recordsets
Create Function