2 Tables - Parse Name Field in Table 1 and Find Value in Table 2

[wallstreet]

Hi, it has been a while since I used Access and I would appreciate some help in the right direction.

Table 1 = Fund_ID, Fund_Name, Fund_Asset_Class

Table 2 = Criteria, Fund_Asset_Class

Basically, I want to look up the Fund_Name in Table 1 against the Criteria field in Table 2. Unfortunately the Criteria and Fund_Name values are not 1 for 1 matches.

Sample Data

Fund_Name = 'CALL UTILITIES SEL SECT'



In Table 2 as a Critieria value I have 'UTILITIES' and the Fund_Asset_Class of 'EQUITY'.

I want to parse the Fund_Name in table 1 of 'CALL UTILITIES SEL SECT', find the Criteria of 'UTILITIES' and return the corresponding Fund_Asset_Class of 'EQUITY' from Table 2.

[June7]

Try:

DLookUp("Fund_Asset_Class", "table2", "InStr('" & [Fund_Name] & "', [Criteria])>0")

[wallstreet]

Hi, I am getting a syntax error (missing operator) in query expression 'InStr('GOV'T & QUALITY BOND', [Criteria])0'

This is what I wrote:
DLookUp("[Fund_Asset_Class]","Rules","InStr('" & [Fund_Name] & "', [Criteria])>0")

Does it matter what field that I put this as a criteria for? Should I be creating a new field in the table?

[June7]

This expression is not intended as criteria for any field. Use it to create a field in query or as the ControlSource for a textbox on report or form.

Could be the apostrophe in GOV'T is causing an issue. Apostrophes have special meaning in SQL and the WHERE argument of DLookup (and other domain aggregate functions) must adhere to SQL syntax. Apostrophes need to be read in pairs. So to have a literal apostrophe in the string requires doubling it. This can be handled with a Replace function.

DLookUp("[Fund_Asset_Class]","Rules","InStr('" & Replace([Fund_Name],"'","''") & "', [Criteria])>0")

Quote marks have the same issue but are harder to deal with.

[wallstreet]

Ok, I used it to create a new field in the query. The syntax works and the query runs but the query is pulling the wrong data. I do not have any relationship amongst the rules table (table 2) and the list of funds (table 1).

Just looking for the simplest way for a query to look thru a set of fund names and match it to a criteria value in another table and return a corresponding value in that other table.

[June7]

If there were a relationship the DLookup would not be required and this would be simple.

I did a test with data and it works. If you want to provide your project for analysis, will look at. Follow instructions at bottom of my post.

[wallstreet]

June7 the sample data is attached.

Assets.mdb

[June7]

Nice to know the actual names of tables and fields. For the three records in the Allied table, this query

SELECT [Copy Of First Allied].*, DLookUp("[Asset Type]","Rules","InStr('" & Replace([Fund_Name],"'","''") & "', [Criteria])>0") AS AssetCriteriaMatch, DLookUp("[Investment Objective]","Rules","InStr('" & Replace([Fund_Name],"'","''") & "', [Criteria])>0") AS InvestmentCriteriaMatch
FROM [Copy Of First Allied];

produces this output:

FUND_ID	FUND_NAME	TICKER	SEC_TYPE	Asset Type	Investment Objective	AssetCriteriaMatch	InvestmentCriteriaMatch
0000A90MD	CALL UTILITIES SEL SECT S AT 35 000 EXPIRES 01 21			NON-CLASSIFIED	NON-CLASSIFIED	BLEND	BALANCED - DOMESTIC
00014J090	TECHNOLOGY		va	NON-CLASSIFIED	NON-CLASSIFIED	EQUITY	SECTOR - TECH/COMMUNICATIONS
00014J170	GLOBAL EQUITIES		va	NON-CLASSIFIED	NON-CLASSIFIED	BLEND	BALANCED - DOMESTIC

[wallstreet]

I think we are close, how is the first one CALL UTILITIES SEL SECT S AT 35 000 EXPIRES 01 21 finding 'Blend' and 'Balanced-Domestic' in the Rules table. It should be finding the word Utilities and then the corresponding values of 'Equity' and 'Sector - Utilities'. It works of the first word that it finds correct?

[June7]

Yes, this is the conundrum with inexact matching.

A match is made on the first record where Criteria value is found in the Fund_Name field. In this case it is record 39 which has the value 'All' that is found in 'Call'.

As an aid to review the matches, add these expressions to the query:

IDCriteriaMatch: DLookUp("[ID]","Rules","InStr('" & Replace([Fund_Name],"'","''") & "', [Criteria])>0")
CriteriaMatch: DLookUp("[Criteria]","Rules","InStr('" & Replace([Fund_Name],"'","''") & "', [Criteria])>0")

I don't know that there can be a programmatic solution to your situation.

[wallstreet]

Hmmm, on an exact match, why did it match 'All' with 'Call'? Any other ideas other than parsing the words out manually and loading them into individual columns?

[June7]

This is not 'exact' match. Exact match would be: "CALL UTILITIES SEL SECT S AT 35 000 EXPIRES 01 21" = "CALL UTILITIES SEL SECT S AT 35 000 EXPIRES 01 21"

This is 'inexact' matching. 'All' is alphabetically before 'Utilities'. 'All' is checked against the Fund_Name value before 'Utilities'. 'All' is found in the name so that is the match returned by query.

Parsing in VBA code might be a possibility. Parsing to fields of table would be cumbersome because the Criteria values are not all the same number of words.

I am starting to see a programmatic solution. Will give it some more thought.

[June7]

Here is latest effort. I wrote a VBA function that is called in query with expression - AssetCriteriaMatch: GetType([Fund_Name]). Still not perfect. Can't achieve perfect. Fund name Global Equities matches to Global not Equity criteria value.

Function GetType(strFund As String) As String
Dim i As Integer
Dim strType As String
Dim aryWords
aryWords = Split(strFund, " ")
For i = 0 To UBound(aryWords)
    If strType = "" Then
        strType = Nz(DLookup("[Asset Type]", "Rules", "[Criteria]='" & Replace(aryWords(i), "'", "''") & "'"), "")
    End If
Next
GetType = IIf(strType = "", DLookup("[Asset Type]", "Rules", "InStr('" & Replace(strFund, "'", "''") & "', [Criteria])>0"), strType)
End Function

[wallstreet]

June, I am getting an Undefined fuction 'GetType' in expression. Can you send me back the asssets db so that I can see how you set it up?

[June7]

The function must be in a general module. From the VBA editor menu > Insert > Module

Add this line in module header: Option Explicit.

Copy/paste the suggested code into the module.

Run Debug > Compile

Here is the query SQL:

SELECT [Copy Of First Allied].*, GetType([Fund_Name]) AS AssetCriteriaMatch FROM [Copy Of First Allied];