VBA module

[James K via AccessMonster.com]

I'd like to create a VB module that looks at the table [fullnames] and
deletes records that match in another table [badwords]. Basically, I'm
trying to cull any records that are business related. The string in the
[badwords] table has such things as Corp, Co, Inc, Inc. etc. I'm limited to
1024 characters using the NOT Like "*XYZ*" in the build portion of the query
and 2048 to the SQL portion. The [badwords] table is much more extensive
than that, so thought a VBA modules might work instead.

I'm open to any and all suggestions though.

Regards,
Jim

 

[Guest]

Please post back with what fields you are looking in in [fullnames], and the
structure of the table [badwords] (the fields you want to look in in
[badwords] and we can come up with a solution.

 

[James K via AccessMonster.com]

In Fullnames the field is OWNAME in the table Badwords its CONAMES

Thanks.

Please post back with what fields you are looking in in [fullnames], and the
structure of the table [badwords] (the fields you want to look in in
[badwords] and we can come up with a solution.

I'd like to create a VB module that looks at the table [fullnames] and
deletes records that match in another table [badwords]. Basically, I'm

[quoted text clipped - 8 lines]

Regards,
Jim

 

[Guest]

One thing I forgot to ask. will OWNAME contain only a word in CONAMES or
will OWNAME contain a string that might contain a word in CONAMES? Can
OWNAMES contain more than one CONAME?

In Fullnames the field is OWNAME in the table Badwords its CONAMES

Thanks.

Please post back with what fields you are looking in in [fullnames], and the
structure of the table [badwords] (the fields you want to look in in
[badwords] and we can come up with a solution.

I'd like to create a VB module that looks at the table [fullnames] and
deletes records that match in another table [badwords]. Basically, I'm

[quoted text clipped - 8 lines]

Regards,
Jim

 

[Vincent Johns]

I'd like to create a VB module that looks at the table [fullnames] and
deletes records that match in another table [badwords]. Basically, I'm
trying to cull any records that are business related. The string in the
[badwords] table has such things as Corp, Co, Inc, Inc. etc. I'm limited to
1024 characters using the NOT Like "*XYZ*" in the build portion of the query
and 2048 to the SQL portion. The [badwords] table is much more extensive
than that, so thought a VBA modules might work instead.

I'm open to any and all suggestions though.

Regards,
Jim

Here's a way to do what I think you want:

Suppose your [fullnames] Table contains these records:

fullnamesID Name
------------ ---------------
-1918265089 Japan, Inc.
-1377655737 Acme explosives
-1307179766 Jon Jones
-1081873994 MS Co.

and [badwords] contains these:

badwordsID Word
----------- -----
-1873047895 Inc.
-28616120 Co.

Then if you define a query [Q_ZapBadNames] in this way:

DELETE fullnames.*, fullnames.fullnamesID
FROM fullnames
WHERE (((fullnames.fullnamesID)
In (SELECT fullnames.fullnamesID FROM fullnames, badwords
WHERE (InStr(1,[fullnames]![Name],[badwords]![Word])<>0))
));

and run it, the contents of [fullnames] after running it should look
like this:

[fullnames] (after running the Query):

fullnamesID Name
------------ ---------------
-1377655737 Acme explosives
-1307179766 Jon Jones


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Good idea, Vincent, except I think the OP wanted to delete the records.

I'd like to create a VB module that looks at the table [fullnames] and
deletes records that match in another table [badwords]. Basically, I'm
trying to cull any records that are business related. The string in the
[badwords] table has such things as Corp, Co, Inc, Inc. etc. I'm limited to
1024 characters using the NOT Like "*XYZ*" in the build portion of the query
and 2048 to the SQL portion. The [badwords] table is much more extensive
than that, so thought a VBA modules might work instead.

I'm open to any and all suggestions though.

Regards,
Jim

Here's a way to do what I think you want:

Suppose your [fullnames] Table contains these records:

fullnamesID Name
------------ ---------------
-1918265089 Japan, Inc.
-1377655737 Acme explosives
-1307179766 Jon Jones
-1081873994 MS Co.

and [badwords] contains these:

badwordsID Word
----------- -----
-1873047895 Inc.
-28616120 Co.

Then if you define a query [Q_ZapBadNames] in this way:

DELETE fullnames.*, fullnames.fullnamesID
FROM fullnames
WHERE (((fullnames.fullnamesID)
In (SELECT fullnames.fullnamesID FROM fullnames, badwords
WHERE (InStr(1,[fullnames]![Name],[badwords]![Word])<>0))
));

and run it, the contents of [fullnames] after running it should look
like this:

[fullnames] (after running the Query):

fullnamesID Name
------------ ---------------
-1377655737 Acme explosives
-1307179766 Jon Jones


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Good idea, Vincent, except I think the OP wanted to delete the records.

I'm not sure what you mean by "except". I assumed that he wanted to
delete them; that's why I suggested a Delete Query. What I didn't
address was how to do that under program control, which would involve
either a Macro or a VB statement to run the Query. But after running
such a Query (via any means), any records deleted from the Table stay
deleted.

To recover the space vacated by deleted records, one can run Tools -->
Database Utilities --> Compact and Repair Database.

:

I'd like to create a VB module that looks at the table [fullnames] and
deletes records that match in another table [badwords]. Basically, I'm
trying to cull any records that are business related. The string in the
[badwords] table has such things as Corp, Co, Inc, Inc. etc. I'm limited to
1024 characters using the NOT Like "*XYZ*" in the build portion of the query
and 2048 to the SQL portion. The [badwords] table is much more extensive
than that, so thought a VBA modules might work instead.

[...]