Replact function - Alternative

[cynteeuh]

I use the below statement in a query to extract an extension from a phone
number that is stored as (504) 123-4567. However, the "replace" function
seems to be preventing me from sharing this query with another application
via odbc. Is there another way write the below without the replace function?

Ext: IIf([StudioId]="EAC","1" &
Right([PhoneNumber],4),IIf([StudioId]="EAUK",Right([PhoneNumber],5),Replace(Right([PhoneNumber],6),"-","")))

Thanks in advance for your help!

 

[Chaim]

You could concatenate the two pieces of your phone number. Something along
the lines of:

Left([Phone Number], InStr(1, [PhoneNumber], "-") - 1) & Right ([Phone
Number], InStrRev([Phone Number], "-") + 1)

My string position math might be off (the +/- 1), but the idea is as above.

Good Luck!

 

[cynteeuh]

I don't quite understand and it didn't work. This returned too many characters.

You could concatenate the two pieces of your phone number. Something along
the lines of:

Left([Phone Number], InStr(1, [PhoneNumber], "-") - 1) & Right ([Phone
Number], InStrRev([Phone Number], "-") + 1)

My string position math might be off (the +/- 1), but the idea is as above.

Good Luck!
--

Chaim

I use the below statement in a query to extract an extension from a phone
number that is stored as (504) 123-4567. However, the "replace" function
seems to be preventing me from sharing this query with another application
via odbc. Is there another way write the below without the replace function?

Ext: IIf([StudioId]="EAC","1" &
Right([PhoneNumber],4),IIf([StudioId]="EAUK",Right([PhoneNumber],5),Replace(
Right([PhoneNumber],6),"-","")))

Thanks in advance for your help!

 

[Chaim]

The idea is: Given a typical phone number entered in a TEXT field as: (123)
456-7890, you can break it into two strings- (123)456 and 7890- by splitting
it at the '-'. Left([phone number], 8) returns the (123)456 part and
Right([phone number], 4) returns the 7890 part. The '-' is in neither part.
Now just concatenate the two pieces: Left([phone number], 8) & Right([phone
number], 4), which means (123)456 & 7890 --> (123)4567890.

I need not have bothered with the InStr() function before. The above assumes
that the phone numbers are all entered in this identical format and as text
fields.

And don't post the same question so many times in so many groups. A little
patience will get you answers.

--

Chaim

I don't quite understand and it didn't work. This returned too many characters.

You could concatenate the two pieces of your phone number. Something along
the lines of:

Left([Phone Number], InStr(1, [PhoneNumber], "-") - 1) & Right ([Phone
Number], InStrRev([Phone Number], "-") + 1)

My string position math might be off (the +/- 1), but the idea is as above.

Good Luck!
--

Chaim

I use the below statement in a query to extract an extension from a phone
number that is stored as (504) 123-4567. However, the "replace" function
seems to be preventing me from sharing this query with another application
via odbc. Is there another way write the below without the replace function?

Ext: IIf([StudioId]="EAC","1" &

Right([PhoneNumber],4),IIf([StudioId]="EAUK",Right([PhoneNumber],5),Replace(
Right([PhoneNumber],6),"-","")))

Thanks in advance for your help!

 

[cynteeuh]

Ok, I understood that explanation! I adjusted it a bit (used the Mid
function) to return the numbers I needed (see below). Sorry for the multiple
listings! I'm new to this and wanted to post them in other categories to get
a response.

Ext: IIf([StudioId]="EAC","1" &
Right([PhoneNumber],4),IIf([StudioId]="EAUK",Right([PhoneNumber],5),Mid([phonenumber],9,1) & Right([phonenumber],4)))

The idea is: Given a typical phone number entered in a TEXT field as: (123)
456-7890, you can break it into two strings- (123)456 and 7890- by splitting
it at the '-'. Left([phone number], 8) returns the (123)456 part and
Right([phone number], 4) returns the 7890 part. The '-' is in neither part.
Now just concatenate the two pieces: Left([phone number], 8) & Right([phone
number], 4), which means (123)456 & 7890 --> (123)4567890.

I need not have bothered with the InStr() function before. The above assumes
that the phone numbers are all entered in this identical format and as text
fields.

And don't post the same question so many times in so many groups. A little
patience will get you answers.

--

Chaim

I don't quite understand and it didn't work. This returned too many characters.

You could concatenate the two pieces of your phone number. Something along
the lines of:

Left([Phone Number], InStr(1, [PhoneNumber], "-") - 1) & Right ([Phone
Number], InStrRev([Phone Number], "-") + 1)

My string position math might be off (the +/- 1), but the idea is as above.

Good Luck!
--

Chaim


I use the below statement in a query to extract an extension from a phone
number that is stored as (504) 123-4567. However, the "replace" function
seems to be preventing me from sharing this query with another application
via odbc. Is there another way write the below without the replace
function?

Ext: IIf([StudioId]="EAC","1" &

Right([PhoneNumber],4),IIf([StudioId]="EAUK",Right([PhoneNumber],5),Replace(
Right([PhoneNumber],6),"-","")))

Thanks in advance for your help!