I need help revising a regex function that was created for me earlier this year by aikimark: https://www.experts-exchange.com/questions/29174458/Query-Help-possible-VBA-solution-Array.html
. The link points to the original solution however the attached db contains an updated version of the function.
The purpose of the function is to identify records in [T_MMA].[Business_Names_TX]
with business/corporate names –vs- individual/proper names, based on a table of business codes, [T_BusinessCodes].[Code], which consists of abbreviations, single words, or phrases. This table is updated frequently based on new business names that are not being captured.
I have been running this function every month on data from non-US countries, which have consisted (mostly) of true corporate names. This month however, I have a set of US data with many individual/proper names and I realize something is not right as the function is capturing every record.
I believe the VBA code is currently written to match the [Code] “anywhere within” [Business_Names_TX]. For more accuracy however, I want to change this so that a match will occur only if the entire [Code]
is found in [Business_Names_TX], using spaces to isolate the [Code] in one of three scenarios:
Beginning of Field: [Code]_space_*any character*
Within Field: *any character*_space_[Code]_space_*any character*
End of field: *any character*_space_[Code]To be clear, there should be no adjustment to the Codes [Code] in the T_BusinessCodes table.
Query ‘q_MMA_Updt’ in the attached file shows those records the Regex function is identifying as ‘Corp’ but that I have identified (manually) as ‘Ind’. Of the first 300 records that are selected, I see only 3 that should be included:
110432: “– ingaged”;
131249: “custom” or “components”.
Thanks in advance for any help.