Using 'Replace' function in Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am running a query which takes one table, formats data, and appends data
to a blank table. I am trying to format the phone number like so:

123/456-7890

I am using the replace function to change all of the () like so:
(123)456-7890

However, since there are two parentheses, I am trying to run two Replaces at
once:

NewPhone: (Replace([phone],")","/",1,1) And Replace([phone],"(","",1,1))

So basically i am replacine ) with / starting from the first character and
doing one replacement.

Can someone please check to make sure I am doing this correctly; when i run
the query I get -1 appended to each record in the phone column on the table.
Thanks in advance.

-Rob
 
perfect. it works. thank you.



Klatuu said:
Try it this way:
NewPhone: Replace(Replace([phone],"(",""),")","/")

Rob said:
Hello,

I am running a query which takes one table, formats data, and appends data
to a blank table. I am trying to format the phone number like so:

123/456-7890

I am using the replace function to change all of the () like so:
(123)456-7890

However, since there are two parentheses, I am trying to run two Replaces at
once:

NewPhone: (Replace([phone],")","/",1,1) And Replace([phone],"(","",1,1))

So basically i am replacine ) with / starting from the first character and
doing one replacement.

Can someone please check to make sure I am doing this correctly; when i run
the query I get -1 appended to each record in the phone column on the table.
Thanks in advance.

-Rob
Click to expand...
Click to expand...
 
you are welcome
it does work, but it sure looks weird :)

Rob said:
perfect. it works. thank you.



Klatuu said:
Try it this way:
NewPhone: Replace(Replace([phone],"(",""),")","/")

Rob said:
Hello,

I am running a query which takes one table, formats data, and appends data
to a blank table. I am trying to format the phone number like so:

123/456-7890

I am using the replace function to change all of the () like so:
(123)456-7890

However, since there are two parentheses, I am trying to run two Replaces at
once:

NewPhone: (Replace([phone],")","/",1,1) And Replace([phone],"(","",1,1))

So basically i am replacine ) with / starting from the first character and
doing one replacement.

Can someone please check to make sure I am doing this correctly; when i run
the query I get -1 appended to each record in the phone column on the table.
Thanks in advance.

-Rob
Click to expand...
Click to expand...
Click to expand...
 
-1 AND -1 = -1
0 AND -1 = 0
0 AND 0 = 0

(Replace([phone],")","/",1,1) AND Replace([phone],"(","",1,1)) = -1,
because, well both conditions evaluate to True(-1), and when you AND them,
they result in True(-1), I'd guess.

Try using the Ampersand(&) instead . It's the concatenation operator.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html
 
Back
Top