Query Question

[Richard]

One of my fields Phone2 is set up for phone numbers. All the numbers were 10
digit. Then I ran a query to add a "1" making the field 11 digit i.e. 1 xxx
xxx-xxxx with this criteria "1" & [phone2] this added the desired 1 but, it
also added a "1" to any blank fields which there are many. How do I get rid
of the extra "1"

Richard

 

[Philip Herlihy]

One of my fields Phone2 is set up for phone numbers. All the numbers were 10
digit. Then I ran a query to add a "1" making the field 11 digit i.e. 1 xxx
xxx-xxxx with this criteria "1" & [phone2] this added the desired 1 but, it
also added a "1" to any blank fields which there are many. How do I get rid
of the extra "1"

Richard

The way I'd do this is to use an update query. I'm not certain from
your description that this is what you've done. The trick is to create
a select query which selects the items you'd want to change, check
you're getting the right records, and then convert it to an update query.

On your first run you'd have wanted to exclude those number fields which
were blank (null?) and update the number as you did. Now, with a lot of
numbers which contain just "1", you'd want to select those, and convert
the query to update the field to null.

Note that I'm assuming that you're storing digits in a plain text field,
and that empty fields contain(ed) null, and not a space, for example, or
the string "none".

Phil, London

 

[John Spencer]

The SQL for such a query would probably look like the following.
UPDATE [YourTable]
SET Phone2 = NULL
WHERE Phone2 ="1"

If you can only use the query design tool and cannot build a query in
the SQL window

-- New Query
-- Select your table
-- Select the Phone2 field
-- Enter the following in the criteria
= 1
Access will add quote marks around the 1 if the field is a text field
-- Select Query: Update from the menu
-- Insert the following in the UPDATE TO
NULL

If you want to see what will get updated, change the view to datasheet.
If you want to execute the query and make the changes, select Query:
Run from the menu.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Richard]

The SQL for such a query would probably look like the following.
UPDATE [YourTable]
SET Phone2 = NULL
WHERE Phone2 ="1"

If you can only use the query design tool and cannot build a query in
the SQL window

This worked like a charm:

If you can only use the query design tool and cannot build a query in
the SQL window

-- New Query
-- Select your table
-- Select the Phone2 field
-- Enter the following in the criteria
= 1
Access will add quote marks around the 1 if the field is a text field
-- Select Query: Update from the menu
-- Insert the following in the UPDATE TO
NULL

Thanks to both of you for your time.
Richard

-- New Query
-- Select your table
-- Select the Phone2 field
-- Enter the following in the criteria
= 1
Access will add quote marks around the 1 if the field is a text field
-- Select Query: Update from the menu
-- Insert the following in the UPDATE TO
NULL

If you want to see what will get updated, change the view to datasheet.
If you want to execute the query and make the changes, select Query:
Run from the menu.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

One of my fields Phone2 is set up for phone numbers. All the numbers were 10
digit. Then I ran a query to add a "1" making the field 11 digit i.e. 1 xxx
xxx-xxxx with this criteria "1" & [phone2] this added the desired 1 but, it
also added a "1" to any blank fields which there are many. How do I get rid
of the extra "1"

Richard