Allowable text phrases

Posted on 2011-05-10
Last Modified: 2012-05-11

I am working with a large table with Federal Express and American Express transactions.  I am trying to group the transactions based on a few of the fields.  And based on what's in there I want to create a Category item/field.  For example, the new field is Category.  If "ship" or "ABC Co." or "XYZ" exists inside the "Shipper" field, I'd like to assign "Cost of Goods Sold", if "Expedia" or "Travelocity", I want it to say "Travel".  I'd like to have a table somewhere with two columns, one for search text and one for assignToCategory.  Then, in this query, I'd search the field on each record through this other table and return the value.  I'd like to make it easy on myself and just be able to add values to the table.  Does anyone know how I could approach this?
Question by:BBlu
    LVL 12

    Expert Comment

    You can write a custom Function for this;

    Public Function fCategory(Phrase As String) As String

    If IsNull(Phrase) Then
    Category = Null

    Select Case Phrase
    Case "ship" Or "ABC Co." Or "XYZ" Or "Shipper"
    fCategory = "Cost of Goods Sold"
    Case "Expedia" Or "Travelocity"
    fCategory = "Travel"
    ... etc.
    End Select

    End Function

    Place the function into a new Module in your database and Save it.

    Then you can use the function fCategory([YourSearchFieldName]) in your Form/Query/Report.


    Author Comment

    That's kind of what I was going to do, but thought there might be an easier way via an independent table through which each record ran to check.
    LVL 12

    Expert Comment

    You can offcourse do this in a Table, something like:

    PhraseID           AutoNum
    Phrase              Text
    CategoryID        Number

    CategoryID        AutoNum
    Category           Text

    Link the Category table to tblPhrases... That would work.
    Enter each Phrase and their Category.


    Author Comment

    Hey Daniel-

    But the phrases won't be exact matches.  Now that I look at it, I don't think the first Select Case would work either.  I'm looking in the Description field for "ABC Co." anywhere in the field.  Any ideas on how to address that?

    Author Comment

    I think this might work but I don't know how to do it.

    1.  I have my main table with the Description fields and records I'm trying to categorize.
    2.  I create another table (CategoryTable) that lists the different keywords that might appear in the Description field and, if found, what category that should be assigned to.
    3.  I create a function with 1 argument, the Description field
    4.  With each record in the main table, I loop through the Category table.
    5.  With each record in the Category Table, I check instr([Description],[phraseToLookFor]) and if the value >0, we assign the Category to that field, if not we move to the next record in the CategoryTable.

    I've attached some code that I've worked on after I found some info. via Google.  It's way off and doesn't work, but it gives an idea of what I think might work.

    Sub getCategory()
    Dim rs As ADODB.Recordset
    Dim sSQL As String
    Dim sValue As String
    sSQL = "SELECT * FROM T_CategoryAssignment"
    Set rs = New ADODB.Recordset
    rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    DoCmd.SetWarnings False
    Do Until rs.EOF
    sValue = rs("Description").Value
    MsgBox (sValue)
    'DoCmd.RunSQL sSQL
    DoCmd.SetWarnings True
    End Sub

    Open in new window

    LVL 44

    Expert Comment

    It would help if you would provide the table name rather than "I am working with a large table..."  - same goes for the field names.

    Accepted Solution

    Here's what I came up with after getting a lot of help.  The fields and tables are listed in the code.  I'm open to hearing how others would do it before I close it with my solution, especially since my thoughts were to do it with a function.
    Private Sub Process_Click()
        DoCmd.SetWarnings False
        'Dim stDocName As String
        Dim DBS As Database
        Dim qdfRB, qdfTree As QueryDef
        Dim DataRecsRB As Recordset
        Dim StrSqlRB, StrSqlTree As String
        Set DBS = CurrentDb
        StrSqlRB = " SELECT T_CategoryAssign.ID, T_CategoryAssign.KeyPhrase, T_CategoryAssign.Category " _
                 & " FROM T_CategoryAssign " _
                 & " ORDER BY T_CategoryAssign.ID;"
        Set qdfRB = DBS.CreateQueryDef("", StrSqlRB)
        Set DataRecsRB = qdfRB.OpenRecordset()
        If DataRecsRB.RecordCount > 0 Then
                   Do Until DataRecsRB.EOF
                        If Not IsNull(DataRecsRB![KeyPhrase]) Then
                                ' UPDATE INFORMATION - T_AmexFedexTxns.[Category]
                                DoCmd.RunSQL " Update T_AmexFedexTxns Set T_AmexFedexTxns.[Category] = """ & DataRecsRB![Category] & """" _
                                           & " WHERE T_AmexFedexTxns.[Recipient Company] Like  """ & "*" & DataRecsRB![KeyPhrase] & "*" & """ ;"
                        End If
        End If
    End Sub

    Open in new window


    Author Closing Comment

    No one had any alternatives to the ones I provided.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now