Importing data from spreadsheets - phone data

[MaliciousMike]

Hello peeps.

Before i continue:
I know very well about excel's annoying removal of leading 0's. And i know it can be solved by making the column type, Text, not a number.

What i'm working on is an excel spreadsheet importing module which validates data from clients and sends error reports or, if all is fine, imports into our system. Problem is, the phone fields might not always be text, as some of them use our template, and some have an automatic system to pull out data... which sucks!

I'm doing it differently than the norm, so I need to stay as far away from hard coding the validation as possible (user friendly system). I have many tables which my code looks at to find out what to do (column names, mandatory fields, etc.)

One part of my system is a table called tblImportExtraChecks which has the fields:
ID (pk - auto) - obviously...
Query (text) - The name of the query to check the data
errMsg (text) - The wording of the error if the query doesn't return null
Description (Memo) - used for the User form so people can describe what the query does and why

What i want to do is create a query which checks telephone numbers in 3 telephone fields.
I'm from the UK, so a standard number would be 07654321098, 01234567890.
The field needs to be accurate as we use AutoDailling.

How do i do this in a query that must return Null if everything is ok?



Additional info:
My system checks the excel spreadsheet, not a table. This checks each row, column by column for column headings and mandatory fields only.
Afterwards, if all is ok, it imports this data into a table (I know it will fit because i've checked column headings). This table, will all the data in it, is queried using these userbuilt queries.

 

[MaliciousMike]

Nevermind... I did this ol' beaut:

IIf(IsNull([Phone1]),IsNumeric([Phone1]))

IsNumeric can be used on a field of any data type (mine were text) and returns True or False.
If everything in the field is a number, it will return true.
This works perfectly for international numbers as well.

Note that it will return false if there are any characters that are not numbers, so 01234 567 890 will be false. Which is good because most auto dailers would require 01234567890.

 

[MaliciousMike]

forgot to mention that all numbers that are outgoing in the UK start with a 0. Inland or international (+44 == 0044)

So additionally I've added:
IIf(Not IsNull([Phone1]) And Left([Phone1],1)<>"0",False,True)


I hope this helps someone!

SELECT IIf(Not IsNull([Phone1]),IsNumeric([Phone1])) AS Phone1Valid, IIf(Not IsNull([Phone2]),IsNumeric([Phone2])) AS Phone2Valid, IIf(Not IsNull([Phone3]),IsNumeric([Phone3])) AS Phone3Valid, IIf(Not IsNull([Phone1]) And Left([Phone1],1)<>"0",False,True) AS Phone1Valid2, IIf(Not IsNull([Phone2]) And Left([Phone2],1)<>"0",False,True) AS Phone2Valid2, IIf(Not IsNull([Phone3]) And Left([Phone3],1)<>"0",False,True) AS Phone3Valid2
FROM tblImportCheck2
WHERE (((IIf(Not IsNull([Phone1]),IsNumeric([Phone1])))=False)) OR (((IIf(Not IsNull([Phone2]),IsNumeric([Phone2])))=False)) OR (((IIf(Not IsNull([Phone3]),IsNumeric([Phone3])))=False)) OR (((IIf(Not IsNull([Phone1]) And Left([Phone1],1)<>"0",False,True))=False)) OR (((IIf(Not IsNull([Phone2]) And Left([Phone2],1)<>"0",False,True))=False)) OR (((IIf(Not IsNull([Phone3]) And Left([Phone3],1)<>"0",False,True))=False));