Stripping Info From Record During Query

  • Thread starter Thread starter Goobz
  • Start date Start date
G

Goobz

Is there a way to strip characters returned from the Query!? For
instance...

I have a script file that has been written to import stuff into our
Active Directory. However, as most of you know, AD ads the "+" in the
country code, when you ad someones phone number...

The last person that was inputting stuff into the database I'm working
from, input the "+" in the field with the phone number.. So the field
looks like "+93 2345 3245342523", or "+01 123 2342-234234".

So the info I need, and the + are in the same field. Now I know you
can add a plus before the result if it doesn't exist in the field, but
I don't know about stripping it...
 
Hi,
use an update query like this
UPDATE tblContacts SET tblContacts.Phone = Mid([Phone],2);
where the table is called tblContacts and the field is called Phone.

Jeanette Cunningham
 
Is there a way to strip characters returned from the Query!? For
instance...

I have a script file that has been written to import stuff into our
Active Directory. However, as most of you know, AD ads the "+" in the
country code, when you ad someones phone number...

The last person that was inputting stuff into the database I'm working
from, input the "+" in the field with the phone number.. So the field
looks like "+93 2345 3245342523", or "+01 123 2342-234234".

So the info I need, and the + are in the same field. Now I know you
can add a plus before the result if it doesn't exist in the field, but
I don't know about stripping it...
Click to expand...

You can use the Replace() function to strip the plus:

ShowPhone: Replace([Phone], "+", "")

will replace all + characters with an empty string.

John W. Vinson [MVP]
 
Is there a way to strip characters returned from the Query!? For
instance...
Click to expand...
I have a script file that has been written to import stuff into our
Active Directory. However, as most of you know, AD ads the "+" in the
country code, when you ad someones phone number...
Click to expand...
The last person that was inputting stuff into the database I'm working
from, input the "+" in the field with the phone number.. So the field
looks like "+93 2345 3245342523", or "+01 123 2342-234234".
Click to expand...
So the info I need, and the + are in the same field. Now I know you
can add a plus before the result if it doesn't exist in the field, but
I don't know about stripping it...
Click to expand...

You can use the Replace() function to strip the plus:

ShowPhone: Replace([Phone], "+", "")

will replace all + characters with an empty string.

             John W. Vinson [MVP]
Click to expand...

Worked beautifully... Thank you...
Now is there a way to get it to strip blank entries, so if the record
has no phone number, instead of returning a record that has an empty
field, it just doesn't show the record? I guess I could do a if not
null, huh!?
 
Worked beautifully... Thank you...
Now is there a way to get it to strip blank entries, so if the record
has no phone number, instead of returning a record that has an empty
field, it just doesn't show the record? I guess I could do a if not
null, huh!?
Click to expand...

Sure, in the query criteria.

John W. Vinson [MVP]
 
Sure, in the query criteria.

             John W. Vinson [MVP]
Click to expand...

Now for some ungawdly reason, I can't remember the correct command to
insert a character when producing a query, similar to the Replace
function... Can someone post the quick code for me!?
 
Now for some ungawdly reason, I can't remember the correct command to
insert a character when producing a query, similar to the Replace
function... Can someone post the quick code for me!?
Click to expand...

What do you want to insert, where, and why???

More info please - the SQL of the current query and what you want to
accomplish for example!

John W. Vinson [MVP]
 
What do you want to insert, where, and why???

More info please - the SQL of the current query and what you want to
accomplish for example!

             John W. Vinson [MVP]
Click to expand...

Basically, the opposite of

ShowPhone: Replace([Phone], "+", "").

Using it to add a "+" in front of the phone number.
 
What do you want to insert, where, and why???

More info please - the SQL of the current query and what you want to
accomplish for example!

             John W. Vinson [MVP]
Click to expand...

Basically, the opposite of

ShowPhone: Replace([Phone], "+", "").

Using it to add a "+" in front of the phone number.
Click to expand...

"+" & [Phone]

The & operator concatenates two string values.

John W. Vinson [MVP]
 
Back
Top