Transferring contents of 1 cell to another

F

faxylady

Contact data from the web is downloaded into Outlook. From Outlook, it is
imported into Access. Some sites list a BusinessPhone2 field in addition to
the fax and phone number. Quite often, this BusinessPhone2 is the contact's
fax number. How do I transfer the contents of the BusinessPhone2 field into
the fax field when the fax field is null? If the contact lists a fax number,
there is no need to insert the BusinessPhone2 in the fax field. However,
when the fax number is blank, I would like to use the BusinessPhone2 field
content in the fax field. As always, your help is most appreciated. Thanks.
 
J

John Spencer

Use an update query.

Standard advice, back up your data BEFORE you try this. Your only recovery
option may be to use the backup.

UPDATE YourTable
SET FaxPhone = [BusinessPhone2]
WHERE FaxPhone is Null or FaxPhone = ""

IF at the same time you want to clear BusinessPhone 2

UPDATE YourTable
SET FaxPhone = [BusinessPhone2],
BusinessPhone2 = Null
WHERE FaxPhone is Null or FaxPhone = ""

If you don't know how to use the above, post back and ask for instructions
on how to do this using the query grid.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
J

John W. Vinson

Contact data from the web is downloaded into Outlook. From Outlook, it is
imported into Access. Some sites list a BusinessPhone2 field in addition to
the fax and phone number. Quite often, this BusinessPhone2 is the contact's
fax number. How do I transfer the contents of the BusinessPhone2 field into
the fax field when the fax field is null? If the contact lists a fax number,
there is no need to insert the BusinessPhone2 in the fax field. However,
when the fax number is blank, I would like to use the BusinessPhone2 field
content in the fax field. As always, your help is most appreciated. Thanks.
Click to expand...

An Update query will work here.

Create a query based on the table. Add the Businessphone2 and Fax fields. Put
a criterion on Fax of

IS NULL

to select only those records which don't already have a Fax value, and

IS NOT NULL

on BusinessPhone2 to select only those records which have data to transfer.

Change the query to an Update query and put

[BusinessPhone2]

*with* the brackets on the Update To line underneath Fax (if you leave off the
brackets it will update the fax number to "BusinessPhone2" - the text
string!).

Run the query by clicking the ! icon and you're done.

John W. Vinson [MVP]
 
F

faxylady

Yes, please tell me how to do this using the query grid. If you would, also
explain how to do this without the query grid. Thank you again.

John Spencer said:
Use an update query.

Standard advice, back up your data BEFORE you try this. Your only recovery
option may be to use the backup.

UPDATE YourTable
SET FaxPhone = [BusinessPhone2]
WHERE FaxPhone is Null or FaxPhone = ""

IF at the same time you want to clear BusinessPhone 2

UPDATE YourTable
SET FaxPhone = [BusinessPhone2],
BusinessPhone2 = Null
WHERE FaxPhone is Null or FaxPhone = ""

If you don't know how to use the above, post back and ask for instructions
on how to do this using the query grid.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

faxylady said:
Contact data from the web is downloaded into Outlook. From Outlook, it is
imported into Access. Some sites list a BusinessPhone2 field in addition
to
the fax and phone number. Quite often, this BusinessPhone2 is the
contact's
fax number. How do I transfer the contents of the BusinessPhone2 field
into
the fax field when the fax field is null? If the contact lists a fax
number,
there is no need to insert the BusinessPhone2 in the fax field. However,
when the fax number is blank, I would like to use the BusinessPhone2 field
content in the fax field. As always, your help is most appreciated.
Thanks.
Click to expand...
Click to expand...
 
F

faxylady

This was tried previously. The first time I left off the brackets and the
text string BusinessPhone2 was inserted into the fax fields. I just tried
again following your directions and am now being asked to Enter a Parameter
Value for BusinessPhone2. Why is this happening?

John W. Vinson said:
Contact data from the web is downloaded into Outlook. From Outlook, it is
imported into Access. Some sites list a BusinessPhone2 field in addition to
the fax and phone number. Quite often, this BusinessPhone2 is the contact's
fax number. How do I transfer the contents of the BusinessPhone2 field into
the fax field when the fax field is null? If the contact lists a fax number,
there is no need to insert the BusinessPhone2 in the fax field. However,
when the fax number is blank, I would like to use the BusinessPhone2 field
content in the fax field. As always, your help is most appreciated. Thanks.
Click to expand...

An Update query will work here.

Create a query based on the table. Add the Businessphone2 and Fax fields. Put
a criterion on Fax of

IS NULL

to select only those records which don't already have a Fax value, and

IS NOT NULL

on BusinessPhone2 to select only those records which have data to transfer.

Change the query to an Update query and put

[BusinessPhone2]

*with* the brackets on the Update To line underneath Fax (if you leave off the
brackets it will update the fax number to "BusinessPhone2" - the text
string!).

Run the query by clicking the ! icon and you're done.

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

John W. Vinson

This was tried previously. The first time I left off the brackets and the
text string BusinessPhone2 was inserted into the fax fields. I just tried
again following your directions and am now being asked to Enter a Parameter
Value for BusinessPhone2. Why is this happening?
Click to expand...

That suggests that there is no field in your table named BusinessPhone2.

Could you open the query in SQL view and post the SQL text here; and also list
the (relevant) fieldnames in your table? Note that the field names
[BusinessPhone2], [Business Phone 2] and [BusinessPhone 2] are three
altogether different fieldnames as far as Access is concerned!


John W. Vinson [MVP]
 
J

John Spencer

The way to do it without the query grid is to open a new query, don't add
any tables, choose View: SQL from the menu
Copy and paste the SQL I posted earlier and edit the field and table names
to your field and table names.

In the grid
-- Open a new query
-- Select your table
-- Add the Fax and BusinessPhone2 fields to the grid
-- Enter the following as criteria for Fax
Criteria: Is Null or = ""
-- Select Query: Update from the menu
-- Enter [TableName].[BusinessPhone2] in update to for the Fax field
-- Select Query: Run from the menu

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

faxylady said:
Yes, please tell me how to do this using the query grid. If you would,
also
explain how to do this without the query grid. Thank you again.

John Spencer said:
Use an update query.

Standard advice, back up your data BEFORE you try this. Your only
recovery
option may be to use the backup.

UPDATE YourTable
SET FaxPhone = [BusinessPhone2]
WHERE FaxPhone is Null or FaxPhone = ""

IF at the same time you want to clear BusinessPhone 2

UPDATE YourTable
SET FaxPhone = [BusinessPhone2],
BusinessPhone2 = Null
WHERE FaxPhone is Null or FaxPhone = ""

If you don't know how to use the above, post back and ask for
instructions
on how to do this using the query grid.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

faxylady said:
Contact data from the web is downloaded into Outlook. From Outlook, it
is
imported into Access. Some sites list a BusinessPhone2 field in
addition
to
the fax and phone number. Quite often, this BusinessPhone2 is the
contact's
fax number. How do I transfer the contents of the BusinessPhone2 field
into
the fax field when the fax field is null? If the contact lists a fax
number,
there is no need to insert the BusinessPhone2 in the fax field.
However,
when the fax number is blank, I would like to use the BusinessPhone2
field
content in the fax field. As always, your help is most appreciated.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Top