Removing Phone Number Format

[JH40]

Can someone help me remove the phone number formatting on a field? It's a text field that appears as (@@@) @@@-@@@@. I'd like it to appear and just a straight 10 digits with no formatting. Seems simple enough but my efforts are not working. I've tried:

Format([phone],"@@@@@@@@@@")
Format([phone],"##########")
Format([phone],"9999999999")

and

all three above with a CInt in front. Nothing works.

Thanks!

 

[Alansidman]

Have you considered removing the format within the properties of the field within the table.

 

[JH40]

Yes, I tried that but there is an error message that deletes them all from the table. I also need the values available on the table in phone number format for a different report.

 

[Alansidman]

I would suggest you create a new field in your table. Format it as you desire and then run an update query to populate the new field with the data from the old field.

In your reports, use the new field and set the format to telephone (in the reports). If this all works, then delete the original field.

 

[Alansidman]

Took it one step further. I created a table with an input mask for a telephone number. The field is text and the mask is !\(999")"000\-0000;;_
which results in a phone number presentation like (888) 555-1212

In my query I put this Expression in a column that I wanted to have the number appear as 8885551212

NewFieldName:Format([MyOldFieldName],"##########")

the new field was populated properly.

 

[vbaInet]

In JH40's first post, it appears that he or she has already tried that and it didn't work. "#" is a User-defined Number format whereas in this case we are dealing with strings, i.e. @ and & formats.

You will need to use the Mid() and Right() functions to extract each part of the number in the string and concatenate all three together.

Yes, I tried that but there is an error message that deletes them all from the table. I also need the values available on the table in phone number format for a different report.

I don't see how that is possible. All you're doing is removing the Format property, it should not error. You should do what Alan mentioned in his first post and only Format the field in the form or report you wish to have it displayed in that format.

 

[AccessBlaster]

I would run a function on the table to strip out all the non-numeric characters from the phone field. This would leave you with the desired 10 characters. I use a button and a update query to call the function. The query looks like this:

UPDATE Contacts SET Contacts.Phone = fstripToNumbersOnly([Phone])
WHERE (((Contacts.Phone) Like "*[!0-9]*"));

[FONT=Arial]Public Function fStripToNumbersOnly(ByVal varText As Variant) As String[/FONT]
[FONT=Arial]'Takes input and returns only the numbers in the input.  Strips out[/FONT]
[FONT=Arial]'all other characters.  Handles nulls, dates, numbers, and strings.[/FONT]
[FONT=Arial]Const strNumbers As String = "0123456789"[/FONT]
[FONT=Arial]Dim strOut As String[/FONT]
[FONT=Arial]Dim intCount As Integer[/FONT]
[FONT=Arial]If Len(varText & "") = 0 Then[/FONT]
[FONT=Arial]strOut = ""[/FONT]
[FONT=Arial]Else[/FONT]
[FONT=Arial]varText = varText & ""[/FONT]
[FONT=Arial]For intCount = 1 To Len(varText)[/FONT]
[FONT=Arial]  If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then[/FONT]
[FONT=Arial]     strOut = strOut & Mid(varText, intCount, 1)[/FONT]
[FONT=Arial]  End If[/FONT]
[FONT=Arial]Next intCount[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]fStripToNumbersOnly = strOut[/FONT]
[FONT=Arial]End Function[/FONT]

You can now display your phone field anyway you wish.
HTH
Richard

 

[vbaInet]

You will need to use the Mid() and Right() functions to extract each part of the number in the string and concatenate all three together.

These functions are available to you, why create a function that will traverse each character in a string:

Mid("(888) 555-1212", 2, 3) & Replace(Mid("(888) 555-1212", 7, 8), "-", "")

 

[AccessBlaster]

These functions are available to you, why create a function that will traverse each character in a string:

Mid("(888) 555-1212", 2, 3) & Replace(Mid("(888) 555-1212", 7, 8), "-", "")

The bigger question for me is why anyone would want to store special characters such as spaces, parentheses and hyphens just to name a few, in a phone field within a table. Given the fact you can reproduce special characters for forms and reports on the fly.

 

[vbaInet]

The bigger question for me is why anyone would want to store special characters such as spaces, parentheses and hyphens just to name a few, in a phone field within a table. Given the fact you can reproduce special characters for forms and reports on the fly.

That's one good question for JH40.

Or perhaps he's just using the Format() function wrongly.

 

[speakers_86]

You could try format([phone],"0000000000"). I've used that successfully for other uses before.

 

[vbaInet]

You could try format([phone],"0000000000"). I've used that successfully for other uses before.

That will work on numbers, we're dealing with strings:

In JH40's first post, it appears that he or she has already tried that and it didn't work. "#" is a User-defined Number format whereas in this case we are dealing with strings, i.e. @ and & formats.