phone mask 1

[kimtp]

I am using a phone number mask on the gui, store it in Access and then recall it for printing. However, not all records have phone numbers but the masked is printed, ( ) - , with underlines if no number is entered.

I have tried IIF(phone is null,null,phone) or variations using ''. Nothing works.

Can this be done?

Thanx.

Kim

 

[dgillz]

Is your field a number field or a string field? Is the "( ) - " actually a part of the raw data? Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[kimtp]

Field is a string. the ( ) - is saved to the database as part of the mask. It does not appear to be part of the raw data. All I enter is the 10 digits for the phone number.

 

[synapsevampire]

Try:

if mid({table.phone,2,1) = " " then
""
else
{table.phone}

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kimtp]

I tried: IIf (mid(Phone,2,1) ="","",Phone) and nothing showed up in the phone number column. If I use IIf (mid(Phone,2,1) ='','',Phone) the results are the same as what I had before.

Thanx.

Kim

 

[kimtp]

I am using a mask in the gui for the phone number. It appears that the literals (I think that is what they are called - the () - and spaces) are being saved to the database. Then when I call the report into CR the literals are coming with it.

Any way to eliminate these literals?

Thanx.

Kim

 

[Transcend]

If ( ) - is in the database then it is in the data.

You said you tried IIf (mid(Phone,2,1) ="","",Phone)

but the example said to try

if mid({table.phone,2,1) = " " then
""
else
{table.phone}

Which has a space " " not an empty string ""

Does this change anything?
IIf (mid(Phone,2,1) =" ","",Phone)

Transcend
[gorgeous

 

[kimtp]

If I use IIf (mid(Phone,2,1) =" ","",Phone) I get a compile error, expected: end of statement. If I change the " " to ' ' then I get a syntax error because of the "". Change the "" to '', the literals still show up.

Thanx

Kim

 

[Transcend]

What about

IIf (mid(Phone,1,3) ="( )","",Phone)

Transcend

 

[kimtp]

Seems that any time I use double quotes in a select statement, I get the same results. Here is the entire select:
"SELECT IIf([Maidenname]='',[Lastname] & ', ' & " & _ " [Firstname],[Lastname]" & _ " & ', ' & [Firstname]" & _
" & ' (' & [Maidenname] & ')') AS Name," & _
" Members.Address, IIf([City]='','',[City] & ', ' &" & _
" [State] & ' ' & [Zip]) AS CityStateZip, " & _
" IIf(Phone=' ','',Phone) AS Fone," & _
" IIf([Deceased],'D','') AS Dead," & _ " Members.LastName" & _
" FROM Members INNER JOIN Attending ON" & _ " Members.ID = Attending.ID" & _
" ORDER BY Members.LastName"
Thanks

Kim

 

[Transcend]

The other thing to maybe think about is run an update query on your database to set ( ) - to null, and change your app so that if no phone number is entered insert null into the db

Otherwise you'll have to remember to check for ( ) -
instead of null all the time.

Transcend

 

[Naith]

Hey Kim,

If you're working with a later version of Crystal; you may try this out in a formula:

WhilePrintingRecords;
StringVar Phone;

Phone := Replace(Replace(Replace({MyPhoneField},'(',''),')',''),'-','');

If IsNumeric(Phone)
Then Picture(Phone,'(xxx)xxx-xxxx')//[b]OR[/b][code] Then {MyPhoneField}
Else '';

Or, even simpler, if you're confident that all your country codes start with zero, you could just use:

If InStr({MyPhoneField},'0') > 0
Then {MyPhoneField}
Else ''

Naith

 

[kimtp]

Bingo, Naith. Works as I want it to. Thanx.

Kim

 

[lyanch]

You could also use a SQL expression to put null in the blank fields.

nullif(table.field, '( ) - ') will output null if the field equals exactly what is in the 's. You may need to check the exact output of the blank phone numbers to get the spacing correct. Much simple if it will work for you.

Lisa