Find value in field

[Friebo]

Hi,

I'm having troubles with the following:

I have 2 tables, Candidates and Attachments. In the Candidate there is a column called email1.

In the Attachment table, there is a column called Text filled with text. Somewhere in this text there is a email address mentioned (which is email1).

I want to find the records where candidate.email1 is mentioned in the attachment.text column.

I therefore tried the following query:

SELECT candidate_id FROM candidate, attachment WHERE email1 IN(attachment.text)

This is returing the correct value only if there isn't any other text in the attachment.text column, which is not the fact.

Anyone that can help me with this?

[Scott Price]

You have your email1 and attachment.text swapped. The sql you have is looking IN email1 FOR attachment.text.

Regards,
Scott

[missinglinq]

Also, if you actually have a column/field named Text you need to change the name to something else; Text is a Reserved Word in Access, and sooner or later it'll cause you trouble.

Welcome to TheScripts!

Linq ;0)>

[Friebo]

Also, if you actually have a column/field named Text you need to change the name to something else; Text is a Reserved Word in Access, and sooner or later it'll cause you trouble.

Welcome to TheScripts!

Linq ;0)>

Thank you both, I've managed to accomplish it by extracting the email addresses out of the text in excel. However, when I want to run the query

 UPDATE attachment SET attachment.data_item_id= (SELECT Distinct candidate_id FROM candidate, attachment
WHERE candidate.email1= attachment.email);

I'm getting the error "Operation must use an updateable query." I don't see what I'm doing wrong here. Both the attachment.data_item_id and Candidate.candidate_id are numeric and the tables are having a Primary Key.

Running the Select query apart from the Update query does return the expected value.