Work Number Field - show only phone numbers not extensions

[bearcub]

I need two separate pieces of information based upon the phone number field, the phone number itself and the extension in a separate field. I hope asking 2 questions in 1 is okay - I apologize in advance if this is against forum rules.

#1
I have a report where I 'm trying to show work numbers without extensions in one field and the phone extension (if they have one) in another field.

The phone number field has some fields with no phone number, some fields with this format "(###)###-####" and some field with this format "(###) ###-####"

When I use the left function below I get a "Type#" error in the all these fields.

Formula: Left([work_phone],14)

What formula show I use to get (650)333-5555 (165) or (650) 333-5555 (165) to display (650)333-5555 or (650) 333-5555 in the report and have the null fields show nothing?

#2

Also, I would like to extract the extension number, if there is one, in the field next to it. Using the same example I would like to display 165 in a separate field

Thank you in advance,

Michael

 

[Gerald Higgins]

Hi, I can't replicate your problem.

The LEFT() function in Excel should work reasonably well on both alpha and numeric entries.

I can't recall seeing a "TYPE#" error in Excel.

Are you actually using Excel, or perhaps some other spreadsheet package ?
Or are you doing this analysis through VBA ?

 

[bearcub]

Thank you Gerald but I was trying to do this in Access, not Excel.

The database we're using allows for phone extensions in the same field as the regular phone number. I need to find a method to prevent these extra characters from showing in the phone field. I don't know if I have my if statement set up properly or not.

I tried using IIF with IsError function as the statement but it ended up not displaying any numbers even though I had the left argument above in the false condition of the statement. I don't know what I did wrong.


Michael

 

[Joe4]

In the underlying table where these phone numbers are stored, can you tell us the following properties of this phone number field?
- Data Type
- Field Size
- Format
- Input Mask

 

[bearcub]

I believe the data type is text. Yikes that mght be the problem.

I think the field size is 255 and it is a default input mask.

would the text field data type create the problem?

I'm not at the office any won't get there for another few hours but I'll look at the this info when I get there.

Michael

 

[Joe4]

Yes, please confirm those details, as those really hold the key as to what is going on and how to approach solving your issue.
Note that Formats can only be applied to numbers, not text.

Here is an some information that you may find interesting: http://www.utteraccess.com/forum/index.php?showtopic=1001186&view=findpost&p=1001227

 

[Gerald Higgins]

Apologies ! I hadn't noticed this was in the Access part of the forum - I found it in the "Unanswered posts" section. Sorry !

 

[Joe4]

I hadn't noticed this was in the Access part of the forum - I found it in the "Unanswered posts" section

I assume that you mean the "Zero Reply Posts" listing.
Note that all the way over on the right of each line, it shows which forum each thread is found in.
Many people miss that (I did too, initially, when I started using that list!).

 

[bearcub]

thank you Joe for the link. I items are entered like phone numbers not formatted as phone numbers. So it is entered as a text string. Like I mentioned, I'll double check the data type, format, size and input mask when I get to work. I know I set up the formulas properly. What I can't understand is why when I put in the IfError function nested in the IIF with the Left function, it hides all the phone numbers. Too bad there isn't something in Access like in Excel where you can walk through the formula (the Evaluate Formula item on the Formulas ribbon)

 

[bearcub]

Whew! I thought that I had posted to the wrong forum!