Community
Showing results for 
Search instead for 
Do you mean 
Reply

Qry with Phone starts with + sign

Copper Contributor
Posts: 56
Country: Netherlands

Qry with Phone starts with + sign

 

Wind7 Ult - ACT2010 premium

 

I am trying to replace the + sign in  phone field :  +1234567890

and replace the + into 00

in lookup  it does not work with startswith/contains +

 

How to do this??

William

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Qry with Phone starts with + sign

You shouldn't have a + (or any country code or international dialing code) in the phone field as these are handled and stored seperately. 

 

I don't know that the lookup can search on a "+" ... SQL searches need special rules for special characters:

%, @, &, $, #, *, :, ;, ., , , !, ?, <, >, +, =, ^, ~, |, \, /, (, ), [, ], {, }, “, and ‘.

 

You could try using the OLEDB connecter with Excel and use a SQL Query like this to extract the contacts that you need:

http://sqlserver2000.databases.aspfaq.com/how-do-i-search-for-special-characters-e-g-in-sql-server.h...

 

 

Platinum Super Contributor
Posts: 5,275
Country: USA

Re: Qry with Phone starts with + sign

Just to expand on Mike's comments - the '+' in the phone fields is added as part of the format of the field.  Here is a Knowledgebase article which describes how to change the formatting of a phone field for multiple contacts:

How To Replace Phone Format For Multiple Records

 

This process requires copying your phone field to an 'un-used' field, then updating your default phone format, and finally re-copying the data back into the phone field. 

 

And ALWAYS make a backup of your database before making mass updates to your data (File > Backup > Database).

Greg Martin
Sage
Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Qry with Phone starts with + sign

Interesting... you can't type a + in the phone field (even free form), but you can in field format

 

Odd