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

 

[Joe4]

OK, if they are Text, and those are the only formats you can see, then we can use the InStrRev function to find the first space in the string when going from the right (end) to the left (start).
If the space is after the 10 character, then we know there is an extension.

So here are the two formulas:

PhoneNum: IIf(InStrRev([work_phone]," ")>10,Left([work_phone],InStrRev([work_phone]," ")-1),[work_phone])
Extension: IIf(InStrRev([work_phone]," ")>10,Mid([work_phone],InStrRev([work_phone]," ")+1),"")

 

[bearcub]

thank you, I try this when I get back to the office,

so this formula will ignore fields that don't have a value in them (a null field)?

As always, thank you for your help,

Michael

 

[Joe4]

If you may have null entries, we will need to nest another level of checking. I just checked to see if the length of work_phone is greater than 0 like this:

PhoneNum: IIf(Len([work_phone])>0,IIf(InStrRev([work_phone]," ")>10,Left([work_phone],InStrRev([work_phone]," ")-1),[work_phone]),"")
Extension: IIf(Len([work_phone])>0,IIf(InStrRev([work_phone]," ")>10,Mid([work_phone],InStrRev([work_phone]," ")+1),""),"")

 

[bearcub]

thank you,

When I get to work within the next hour or so I'll apply these formulas. I'm sure they will do the trick. I didn't know about the Instr function being instrumental in this situation. Have to get used to Access formulas since I've been working in Excel exclusively for many years.

Michael

 

[Joe4]

Coupled with the InStr function in Access is the InStrRev function. This function is similar to InStr, but works backwards (starts search at the end of the string, looking back). It allows you to easily find the last instance of a character.
This function is awesome! I wish Excel had an equivalent to it.

 

[bearcub]

I noticed that last week. Some of the Excel functions could copy some of the Access functions I agree!