MS Access Criteria Query using other Table fields

[megan80]

Hello,
I have a table containing some Email addresses which I want to check for correctness (data is being recorded after Optical recognition and it does contain errors). I am however not so sure how it would work best.

For example, in my table I have a Column containing the emails, this could look like this:

Petersmith@test.com
3456@test.com
Petersm;th@test.com
(Petersmith@test.com
<armenrodriguez@test.com
samy)fun@test.com
juliacars@test.com
@
maria-luz@test.com

We all know that Email Adresses can't contain ";" or ")" or be just an @

I started filtering on the criteria row of my Access Query like <>"*(*" but then I had to add more and more exceptions so I decided to create a table where I included a list of non permitted caracters.

How can I now apply the filter so that my Query looks at this table and if one of those caracters is contained in the Email it gets excluded or it shows a text (Bad recognition)?

Further I would need to exclude also all emails which contains numbers or are only an @ or @test.com.

Thanks a lot for your help I couldn't find any similar question. Maybe I am thinking too complicated?

 

[alansidman]

Look at this sample db to see an example of how to do this:

https://app.box.com/s/0znyhsknmmo1e22trax343oud730vj1h