Help with "Update" Query

[Maggic]

I am a little hesitant about posting this question as I realise that my
knowledge & expertise in Access is way below all the other posters in this
group, but here goes ....

I am teaching myself Access using the Microsoft "Step by Step for Access 97
Visual Basic". I have set up a data base of customers & am mostly able to
do what I need (with a little help from you guys in the past), but have now
hit a snag. Data I imported from Quickbooks has invalid phone nos on some
records, due to the lack of edits performed by the Quickbooks form. All nos
have a "-" in the middle, and some look like ")869-1119" .

I would like to run an update query that removes both the ")" and the "-" if
present. Sad to say I cannot think of a way to do this, lacking both SQL
and VB knowledge at present. The Msoft Step by Step book doesn't cover this
kind of predicament .. neither do the various other references I have ...
none I expect are technical enough.


Some help with this would be much appreciated.

Maggic

 

[LeAnne]

If the bad phone numbers _always_ appear as ")xxx-xxxx", then use an
Update query (warning, air code):

UPDATE MyTable SET MyTable.PhoneNum = Mid([PhoneNum],2,3) &
Right([PhoneNum],4);
WHERE [PhoneNum] Like ")*";

This will remove the misplaced paren and the dash in the middle. BUT if
some phone nos have no ")", but only a dash, then run a second Update
using the LEFT and RIGHT functions:

UPDATE MyTable SET MyTable.PhoneNum = Left([PhoneNum],3) &
Right([PhoneNum],4);

hth,

LeAnne

 

[Maggic]

Many thanks for your help ... your solution works perfectly .. had to change
your second stmt to include LIKE "*-*" to only change those nos left
with the dash in the middle ... looking forward to learning more SQL as I go
along with this data base ... once again your help is much appreciated.

Maggic

If the bad phone numbers _always_ appear as ")xxx-xxxx", then use an
Update query (warning, air code):

UPDATE MyTable SET MyTable.PhoneNum = Mid([PhoneNum],2,3) &
Right([PhoneNum],4);
WHERE [PhoneNum] Like ")*";

This will remove the misplaced paren and the dash in the middle. BUT if
some phone nos have no ")", but only a dash, then run a second Update
using the LEFT and RIGHT functions:

UPDATE MyTable SET MyTable.PhoneNum = Left([PhoneNum],3) &
Right([PhoneNum],4);

hth,

LeAnne

I am a little hesitant about posting this question as I realise that my
knowledge & expertise in Access is way below all the other posters in this
group, but here goes ....

I am teaching myself Access using the Microsoft "Step by Step for Access 97
Visual Basic". I have set up a data base of customers & am mostly able to
do what I need (with a little help from you guys in the past), but have now
hit a snag. Data I imported from Quickbooks has invalid phone nos on some
records, due to the lack of edits performed by the Quickbooks form. All nos
have a "-" in the middle, and some look like ")869-1119" .

I would like to run an update query that removes both the ")" and the "-" if
present. Sad to say I cannot think of a way to do this, lacking both SQL
and VB knowledge at present. The Msoft Step by Step book doesn't cover this
kind of predicament .. neither do the various other references I have ....
none I expect are technical enough.

Some help with this would be much appreciated.

Maggic