compare one table to another

[mazdaman]

Problem: I want to check the contents of "table2, field 2" with that of "table1, field1" - basically im checking input in memo field in table 2 against a list of abusive terms in table 1. Is there an easy way of doing this (im a novice)

 

[smedvid]

One approach "may" work. Create a record set and loop through the keyword list and perform a search for each word. htih,

Dim ThisDB as DAO.Database
Dim rs1 as DAO.recordset
Dim rs2 as DAO.recordset
Dim lcSQL as String
Dim lcKeyword as String
Dim llErrorFlag as Bool

Set ThisDB = CurrentDB
lcSQL = SELECT Field1 from LookupTable
llErrorFlag=False
Set rs1 = thisdb.openrecordset(lcSQL,snapshot)

If rs1.eof()=false then
rs1.movefirst
Do while rs1.eof()=false
lcKeyword = rs1("Field1")
lcSQL = SELECT Count(*) from table2 Where
Table2.Memofield Like "*" & lcKeyword & "*"
Set rs2 = thisdb.openrecordset(lcSQL,snapshot)
If rs2.eof()=false then
'Something Found?
'Log Error... Report to user When Done
llErrorFlag=True
Else
'Keyword Not Found. - Passes Test

enidf

rs1.movenext
Loop
endif

If llErrorFlag=True
'msgbox - display message... detail form/report..
Endif

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[johnherman]

you can use the WHERE clause

WHERE table2.field2 NOT IN (SELECT table1.field1 FROM table1)

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[MakeItSo]

im checking input in memo field in table 2 against a list of abusive terms in table 1

Steve's approach is a good one (disregarding some typos. ;-)) - once the data is already in the memo field.
However I think you need a check during input resp. before update of the memo field.

How is the data read? ASP page? Access form?
Once we know this, we might be able to help you get an abusive term filter" before the words are stored in the database.

Cheers,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de