copy last four digits of phone number from any column to one colu

[Guest]

I maintain a phone support database. I have multiple phone numbers in
different tables per customer.

Phone numbers are entered in column formatted for phone numbers with the (
);;;-;;;; input mask. Maybe 30% of the numbers do not have the input mask
because the were imported from an earlier database that did not use the mask.

When a user adds a phone number to a record, I want to copy the last four
digits of that phone number (plus phone numbers from other phone columns in
other tables) into a single column so I can search by the last four digits.

Any takers??

 

[Rick B]

To pull the last four in a new column of a query, include something like the
following...

LastFour: Right([PhoneNumber],4)

Then you can put your criteria under that to prompt the user. For
example...

= [Enter Last Four to Locate]

 

[Guest]

Thanks for the reply Rick. I have been experimenting with
Right([PhoneNumber],4) but the query comes back with one row of empty
columns. I have gone to the table to verify I am entering the correct
digits... I tried adding an = but it did not help.

If it makes any difference, I am polling this data from an ODBC connection
to a MySQL db, into linked tables in MSA 2002, and running the query from
there. The value for the cust_phone column is cust_phone varchar(14). Could
it be that the original VB6 interface I use to work in the db is filling in
any remaining char positions with empty spaces?

Also; some numbers appears as 1234567890, 123-456-7890. (123) 456-7890.
obviously this changes how many characters there are, in the column... or
would it matter since the command RIGHT([number],4) is telling it to just
grab the last four characters of the line? Even if it is empty spaces,
characters, or a combo thereof?

To pull the last four in a new column of a query, include something like the
following...

LastFour: Right([PhoneNumber],4)

Then you can put your criteria under that to prompt the user. For
example...

= [Enter Last Four to Locate]



--
Rick B

I maintain a phone support database. I have multiple phone numbers in
different tables per customer.

Phone numbers are entered in column formatted for phone numbers with the (
);;;-;;;; input mask. Maybe 30% of the numbers do not have the input mask
because the were imported from an earlier database that did not use the mask.

When a user adds a phone number to a record, I want to copy the last four
digits of that phone number (plus phone numbers from other phone columns in
other tables) into a single column so I can search by the last four digits.

Any takers??

 

[Rick B]

Where are you putting the "Right([PhoneNumber],4)"?

Did you place it in a new column in your query and precede it with a name
and a colon like I stated...

LastFour: Right([PhoneNumber],4)


--
Rick B

Thanks for the reply Rick. I have been experimenting with
Right([PhoneNumber],4) but the query comes back with one row of empty
columns. I have gone to the table to verify I am entering the correct
digits... I tried adding an = but it did not help.

If it makes any difference, I am polling this data from an ODBC connection
to a MySQL db, into linked tables in MSA 2002, and running the query from
there. The value for the cust_phone column is cust_phone varchar(14). Could
it be that the original VB6 interface I use to work in the db is filling in
any remaining char positions with empty spaces?

Also; some numbers appears as 1234567890, 123-456-7890. (123) 456-7890.
obviously this changes how many characters there are, in the column... or
would it matter since the command RIGHT([number],4) is telling it to just
grab the last four characters of the line? Even if it is empty spaces,
characters, or a combo thereof?

To pull the last four in a new column of a query, include something like the
following...

LastFour: Right([PhoneNumber],4)

Then you can put your criteria under that to prompt the user. For
example...

= [Enter Last Four to Locate]



--
Rick B



"Batteries not included"

wrote in message news:[email protected]... the
mask. columns
in

 

[Guest]

I pasted your string Right([PhoneNumber],4) into the criteria box, of the
cust_phone column...

Where are you putting the "Right([PhoneNumber],4)"?

Did you place it in a new column in your query and precede it with a name
and a colon like I stated...

LastFour: Right([PhoneNumber],4)


--
Rick B

Thanks for the reply Rick. I have been experimenting with
Right([PhoneNumber],4) but the query comes back with one row of empty
columns. I have gone to the table to verify I am entering the correct
digits... I tried adding an = but it did not help.

If it makes any difference, I am polling this data from an ODBC connection
to a MySQL db, into linked tables in MSA 2002, and running the query from
there. The value for the cust_phone column is cust_phone varchar(14). Could
it be that the original VB6 interface I use to work in the db is filling in
any remaining char positions with empty spaces?

Also; some numbers appears as 1234567890, 123-456-7890. (123) 456-7890.
obviously this changes how many characters there are, in the column... or
would it matter since the command RIGHT([number],4) is telling it to just
grab the last four characters of the line? Even if it is empty spaces,
characters, or a combo thereof?

To pull the last four in a new column of a query, include something like the
following...

LastFour: Right([PhoneNumber],4)

Then you can put your criteria under that to prompt the user. For
example...

= [Enter Last Four to Locate]



--
Rick B



"Batteries not included"

wrote in message I maintain a phone support database. I have multiple phone numbers in
different tables per customer.

Phone numbers are entered in column formatted for phone numbers with the (
);;;-;;;; input mask. Maybe 30% of the numbers do not have the input mask
because the were imported from an earlier database that did not use the
mask.

When a user adds a phone number to a record, I want to copy the last four
digits of that phone number (plus phone numbers from other phone columns
in
other tables) into a single column so I can search by the last four
digits.

Any takers??

 

[Rick B]

Nope. That is not what I said to do.

Go back and read my first post. It includes what you should put in the
criteria. and what to put in A NEW COLUMN.



--
Rick B

I pasted your string Right([PhoneNumber],4) into the criteria box, of the
cust_phone column...

Where are you putting the "Right([PhoneNumber],4)"?

Did you place it in a new column in your query and precede it with a name
and a colon like I stated...

LastFour: Right([PhoneNumber],4)


--
Rick B



"Batteries not included"

wrote in message news:[email protected]...

Thanks for the reply Rick. I have been experimenting with
Right([PhoneNumber],4) but the query comes back with one row of empty
columns. I have gone to the table to verify I am entering the correct
digits... I tried adding an = but it did not help.

If it makes any difference, I am polling this data from an ODBC connection
to a MySQL db, into linked tables in MSA 2002, and running the query from
there. The value for the cust_phone column is cust_phone varchar(14). Could
it be that the original VB6 interface I use to work in the db is

filling
in

any remaining char positions with empty spaces?

Also; some numbers appears as 1234567890, 123-456-7890. (123) 456-7890.
obviously this changes how many characters there are, in the column... or
would it matter since the command RIGHT([number],4) is telling it to just
grab the last four characters of the line? Even if it is empty spaces,
characters, or a combo thereof?



:

To pull the last four in a new column of a query, include something

like
the

following...

LastFour: Right([PhoneNumber],4)

Then you can put your criteria under that to prompt the user. For
example...

= [Enter Last Four to Locate]



--
Rick B



"Batteries not included"

wrote in message I maintain a phone support database. I have multiple phone numbers in
different tables per customer.

Phone numbers are entered in column formatted for phone numbers

with
the (

);;;-;;;; input mask. Maybe 30% of the numbers do not have the

input
mask

because the were imported from an earlier database that did not

use
the

mask.

When a user adds a phone number to a record, I want to copy the

last
four

digits of that phone number (plus phone numbers from other phone columns
in
other tables) into a single column so I can search by the last four
digits.

Any takers??

 

[Guest]

Rick:

You rock bud! I looked at this for a long time before I fianlly figured out
what I was doing wrong. After we discussed it a little further, I was
pasting your statement as you wrote it into the Field: box of the query as a
new column... When I changed[PhoneNumber] to [cust_phone] which is the actual
column heading, it worked perfectly.

Mondo thanks for your help, and double mondo thanks for staying on it to the
end.

Peace!

Nope. That is not what I said to do.

Go back and read my first post. It includes what you should put in the
criteria. and what to put in A NEW COLUMN.



--
Rick B

I pasted your string Right([PhoneNumber],4) into the criteria box, of the
cust_phone column...

Where are you putting the "Right([PhoneNumber],4)"?

Did you place it in a new column in your query and precede it with a name
and a colon like I stated...

LastFour: Right([PhoneNumber],4)


--
Rick B



"Batteries not included"

wrote in message Thanks for the reply Rick. I have been experimenting with
Right([PhoneNumber],4) but the query comes back with one row of empty
columns. I have gone to the table to verify I am entering the correct
digits... I tried adding an = but it did not help.

If it makes any difference, I am polling this data from an ODBC connection
to a MySQL db, into linked tables in MSA 2002, and running the query from
there. The value for the cust_phone column is cust_phone varchar(14).
Could
it be that the original VB6 interface I use to work in the db is filling
in
any remaining char positions with empty spaces?

Also; some numbers appears as 1234567890, 123-456-7890. (123) 456-7890.
obviously this changes how many characters there are, in the column... or
would it matter since the command RIGHT([number],4) is telling it to just
grab the last four characters of the line? Even if it is empty spaces,
characters, or a combo thereof?



:

To pull the last four in a new column of a query, include something like
the
following...

LastFour: Right([PhoneNumber],4)

Then you can put your criteria under that to prompt the user. For
example...

= [Enter Last Four to Locate]



--
Rick B



"Batteries not included"
<[email protected]>
wrote in message
I maintain a phone support database. I have multiple phone numbers in
different tables per customer.

Phone numbers are entered in column formatted for phone numbers with
the (
);;;-;;;; input mask. Maybe 30% of the numbers do not have the input
mask
because the were imported from an earlier database that did not use
the
mask.

When a user adds a phone number to a record, I want to copy the last
four
digits of that phone number (plus phone numbers from other phone
columns
in
other tables) into a single column so I can search by the last four
digits.

Any takers??