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

[wallstreet]

The GetType module is showing in the Unrealted Objects category

[wallstreet]

Could it be a Reference that is not checked? In the VB editor? Maybe easier if you send back the DB itself.

[wallstreet]

I got it to work. I named the module the same as the function. Guess you can't do that!

[June7]

That is the case. A module can have many procedures - Sub and Function.

[wallstreet]

June, I am getting a Run-time error '94': Invalid use of Null when I run this across all of the records that I have. What can I add to the function that if the parsed fund name is not found in the criteria rules to return the existing asset classification and not an error?

[June7]

Change last line of function to:

GetType = IIf(strType = "", Nz(DLookup("[Asset Type]", "[Copy Of Rules]", "InStr('" & Replace(strFund, "'", "''") & "', [Criteria])>0"), strFund), strType)

[wallstreet]

Thanks June, that worked. The issue I now see is that I added in another fund 'CALL ISHARES MSCI BRAZIL FREE AT 60 000 EXPIRES 01'. This is matching on the 'All' portion of 'Call'. Is there anyway for it to match only the whole words?

Also, the 'CALL UTILITIES' fund is coming back with Equity as the Asset Type (which is correct) but I put in the CriteriaMatch expression that you suggested and it is matching on the same value as the 'CALL ISHARES' but returning a different value. Weird.

[June7]

Like I said, can't achieve perfect.

'CALL UTILITIES' returns 'Equity'.
'CALL ISHARES...' returns 'Blend'

I don't see them matching on same value. The first matches on UTILITIES in the loop; the second matches on ALL in the second-chance match at end of code. This line tries to find a Rule Criteria anywhere within the Fund Name but only uses the match if the preceding loop is unsuccessful, hence the match between 'CALL ISHARES...' and ALL. So if TECHNOLOGY was not in the Rules a match would be made on criteria TECH for Fund Name of TECHNOLOGY. If you would rather just return 'No Match' for records that don't have a full word match, then the last line could be:

GetType = IIf(strType = "", "No Match", strType)