looking for the text of one field in another

[kattatonic1]

Hi,



I want to return the records whose field [strDetails] (a text field) contains the text from the last name field [strLastName]. I have been playing with strComp and inStr and can't seem to hit it.

Many thanks!

Kay from chilly Toronto today

[kattatonic1]

Maybe I should mention that the two fields to compare are in two different tables.

[ItsMe]

What do you have so far? The field is named strDetails?

[kattatonic1]

In TBL1 the field name is strDetails. It contains contents like "blah blah blah Smith blah". It's a short comment from the HR department.
In TBL2 the other field is named strLastName. We need to know if the person is mentioned in the comment.
So need a criteria that means "return just records where the text of strLastName is contained in the text of strDetails".

Anything I've tried with inStr and strComp have returned no records.

Thanks for any help!

[ItsMe]

I would go after the info in a form where the User is interacting with the data. I would declare a couple string variables and assign the values of a couple bound controls to the variables.

dim strText as string
dim strFind as string

strText = Me.strDetails
strfind = Me.strLastName

if instr(strText, strfind) then
msgbox "Found Name"
else
msgbox "No Match Found"
end if

[kattatonic1]

That actually helps with something else I'm doing.

For this one we are just working on setting up a query that will return the starting data required on certain forms. We haven't started building the forms yet.

[ItsMe]

Try creating an alias field in your Query builder with this in the field
FoundName: instr([TBL1].[strDetails], [TBL2].[strLastName])

and this in the criteria
>=1

You can choose to hide your field