CPU-friendly way to insert wildcards?

[Jaaz81]

OK I need serious help here.

I'm charged with cleaning up duplicated data across names that are not
normalized. Most of the issue is a mix of anacronyms being used and
differences in what the clerks used for separation ("/" vs. "-" for
example). I have a query set up that is in this format (please forgive
the lack of SQL knowledge here)

From Imports

Name: String field

AKA: dlookup("Name","Imports","Name = '" & WildCard([Name]) & "' AND
Name <> '" & [Name] & "'") Criteria: Is Not Null

Currently this is Function WildCard:

Test = Replace(Test, "-", "*", 1, , vbTextCompare)
Test = Replace(Test, "(", "*", 1, , vbTextCompare)
Test = Replace(Test, ")", "*", 1, , vbTextCompare)
Test = Replace(Test, ".", "*", 1, , vbTextCompare)
Test = Replace(Test, ",", "*", 1, , vbTextCompare)
Test = Replace(Test, "[", "*", 1, , vbTextCompare)
Test = Replace(Test, "]", "*", 1, , vbTextCompare)
Test = Replace(Test, "/", "*", 1, , vbTextCompare)
Test = Replace(Test, "\", "*", 1, , vbTextCompare)
Test = Replace(Test, " ", "*", 1, , vbTextCompare)
WildSymbol = Test

Please tell me there's a more CPU-friendly way to do this, the query
will take more than 15 minutes to run and I need to be able to work
with this. Thanks.

 

[kingston via AccessMonster.com]

You might have better luck taking a "brute force" approach in a subroutine.
Create a recordset and then just scan the data for your wildcards. Anything
else you try will do pretty much the same thing if you think about it. You
just want the computer to go through the dataset once rather than multiple
times.

wild = "-().[]/\ "
stringSQL = SELECT Field1 FROM Table1 WHERE Field1 Is Not Null;
rst.openrecordset stringSQL

for i = 1 to rst.recordcount
for j = 1 to len(wild)
if instr(rst!Field1,mid(wild,j,1))> 0 then
replace(rst!Field1,mid(wild,j,1),"*")
end if
next j
next i


HTH.

OK I need serious help here.

I'm charged with cleaning up duplicated data across names that are not
normalized. Most of the issue is a mix of anacronyms being used and
differences in what the clerks used for separation ("/" vs. "-" for
example). I have a query set up that is in this format (please forgive
the lack of SQL knowledge here)

From Imports

Name: String field

AKA: dlookup("Name","Imports","Name = '" & WildCard([Name]) & "' AND
Name <> '" & [Name] & "'") Criteria: Is Not Null

Currently this is Function WildCard:

Test = Replace(Test, "-", "*", 1, , vbTextCompare)
Test = Replace(Test, "(", "*", 1, , vbTextCompare)
Test = Replace(Test, ")", "*", 1, , vbTextCompare)
Test = Replace(Test, ".", "*", 1, , vbTextCompare)
Test = Replace(Test, ",", "*", 1, , vbTextCompare)
Test = Replace(Test, "[", "*", 1, , vbTextCompare)
Test = Replace(Test, "]", "*", 1, , vbTextCompare)
Test = Replace(Test, "/", "*", 1, , vbTextCompare)
Test = Replace(Test, "\", "*", 1, , vbTextCompare)
Test = Replace(Test, " ", "*", 1, , vbTextCompare)
WildSymbol = Test

Please tell me there's a more CPU-friendly way to do this, the query
will take more than 15 minutes to run and I need to be able to work
with this. Thanks.

 

[Jaaz81]

Thanks kingston! I had to re-write a bit of that since VB didn't like
SQL statements, but all in all it worked wonders for speed over what I
had.

-Jaaz

You might have better luck taking a "brute force" approach in a subroutine.
Create a recordset and then just scan the data for your wildcards. Anything
else you try will do pretty much the same thing if you think about it. You
just want the computer to go through the dataset once rather than multiple
times.

wild = "-().[]/\ "
stringSQL = SELECT Field1 FROM Table1 WHERE Field1 Is Not Null;
rst.openrecordset stringSQL

for i = 1 to rst.recordcount
for j = 1 to len(wild)
if instr(rst!Field1,mid(wild,j,1))> 0 then
replace(rst!Field1,mid(wild,j,1),"*")
end if
next j
next i


HTH.

OK I need serious help here.

I'm charged with cleaning up duplicated data across names that are not
normalized. Most of the issue is a mix of anacronyms being used and
differences in what the clerks used for separation ("/" vs. "-" for
example). I have a query set up that is in this format (please forgive
the lack of SQL knowledge here)

From Imports

Name: String field

AKA: dlookup("Name","Imports","Name = '" & WildCard([Name]) & "' AND
Name <> '" & [Name] & "'") Criteria: Is Not Null

Currently this is Function WildCard:

Test = Replace(Test, "-", "*", 1, , vbTextCompare)
Test = Replace(Test, "(", "*", 1, , vbTextCompare)
Test = Replace(Test, ")", "*", 1, , vbTextCompare)
Test = Replace(Test, ".", "*", 1, , vbTextCompare)
Test = Replace(Test, ",", "*", 1, , vbTextCompare)
Test = Replace(Test, "[", "*", 1, , vbTextCompare)
Test = Replace(Test, "]", "*", 1, , vbTextCompare)
Test = Replace(Test, "/", "*", 1, , vbTextCompare)
Test = Replace(Test, "\", "*", 1, , vbTextCompare)
Test = Replace(Test, " ", "*", 1, , vbTextCompare)
WildSymbol = Test

Please tell me there's a more CPU-friendly way to do this, the query
will take more than 15 minutes to run and I need to be able to work
with this. Thanks.