Update query to Phone Number

[nick941741]

Firstly I'm not sure if this is in the right place, so appologies if not.

I have a database that is a montly mobile phone bill, its quite a large one so that's why I use access to then run some queries and supply the user with a breakdown of their bill. 1 thing I've got an issue with is that the number are stored in different formats so we have.

07000123456 and 447000123456

These of course are the same number, I've got a formula I can use in excel to covert these before importing them but ideally I didn't want to touch the file in excel before it gets to access. Can anyone help me covert this to a query.

=IF(C1=121,"121",(IF(LEN(C1)=10,"0"&MID(C1,1,4)&" "&MID(C1,5,6),"0"&MID(C1,3,4)&" "&MID(C1,7,6))))

Thanks

 

[Alansidman]

If you change the IF in your formula to IIF and change C1 to the name of the field holding the number, I think it should work.