Validating phone numbers

[noelgarry]

Hi
I am trying to work with a large table of customer data.
I know that there is a large number of invalid values.
I would like to run a make table query that would check the phone number against a table of known invalid numbers (e.g. 1234567, 11111111, 99999999, etc).
If the number exists on the invalid table, then I would like to replace it with a null value.
Regards
Noel

 

[ajetrumpet]

not sure if you want a make-table here. maybe you should use an UPDATE query instead?

Regardless though, use the "IN" subquery here:

UPDATE (or INSERT INTO)

table.phoneNumberField = 
   IIF(table.phoneNumberField IN 
      (SELECT otherTable.InvalidPhoneNumberField FROM otherTable), 
         NULL, table.PhoneNumberField)