Replace Function

  • Thread starter Thread starter Chuck F via AccessMonster.com
  • Start date Start date
C

Chuck F via AccessMonster.com

Hello,
NOOB HERE

I was wondering if there was a way to replace a multiple numbers with one
universal number. Lets say these are phone numbers without the dashes...

Example:

Change Number: Replace([field5],8005555555,8006666666)

What I would like is to replace a few different numbers with the universal
number 8006666666.

How can I do this?
 
Give us some examples of the original data and what you want the data to be
after the replacement. Kinda difficult to envision what you want to do from
this post's information.
 
Beware that Replace() is a String function and you are feeding it numeric
values ...
 
I would use an update query.

Update [YourTableNameHere]
SET [Field5] = "8006666666"
WHERE [Field5] in ("8005555555","8007777777","8886667777")

You would have to do this for each value that you wanted to end up with.

Another method would be to build a table of original values and replacement
values and use it in a query to specify which values to replace with which
values. Something like

TheReplacementTable
OriginalValue - contents of 8005555555, 8006666666, etc
NewValue - contents of 8006666666 (etc.)

Then an update query of

UPDATE YourOriginalTable as O INNER JOIN TheReplacementTable as R
 
Ok I tried the update query but I guess I was too retarded to figure it out
lol. I am using this beast for now and it seems to work.

Change Number: Mid([Field5],1,4) & IIf(Mid([Field5],5,10)="8005553333",
"8005551111",IIf(Mid([Field5],5,10)="8005554444","8005551111",IIf(Mid([Field5]
,5,10)="8005555555","8005551111",IIf(Mid([Field5],5,10)="8005551111",
"8005551111",IIf(Mid([Field5],5,10)="8005556666","8005556666",IIf(Mid([Field5]
,5,10)="8005552222","8005551111","NULL")))))) & Mid([Field5],15,43)

The original table looks like this:

Call Data
GMD 8005551111200507040127INQ 000001 5551234321
GMD 8005554444200507040848INQ 000001 5558487521
GMD 8005556666200507040900INQ 000001 5629088155
GMD 8005552222200507040903INQ 000001 3149650864

Finished table looks like this:

Change Number
GMD 8005551111200507040127INQ 000001 5551234321
GMD 8005551111200507040848INQ 000001 5558487521
GMD 8005556666200507040900INQ 000001 5629088155
GMD 8005551111200507040903INQ 000001 3149650864

So what I've done is changed all the numbers to 8005551111. 8005556666 also
stayed the same. I know it's a barbaric way of doing things but since I'm so
new to access and I need to make my life easier I'm trying to learn it as
quickly as I can.

Thanks,
I would use an update query.

Update [YourTableNameHere]
SET [Field5] = "8006666666"
WHERE [Field5] in ("8005555555","8007777777","8886667777")

You would have to do this for each value that you wanted to end up with.

Another method would be to build a table of original values and replacement
values and use it in a query to specify which values to replace with which
values. Something like

TheReplacementTable
OriginalValue - contents of 8005555555, 8006666666, etc
NewValue - contents of 8006666666 (etc.)

Then an update query of

UPDATE YourOriginalTable as O INNER JOIN TheReplacementTable as R
ON O.Field5 = R.OriginalValue
SET Field5 = R.NewValue
Hello,
NOOB HERE
Click to expand...
[quoted text clipped - 10 lines]
How can I do this?
Click to expand...
Click to expand...
 
Well, must say that the data you posted seems to be different from the data you
discussed. Your original posting referred to only the 7 digits of the number.
Now, you seem to have one field with several types of information in it.

So, if you got it to work for you, then go for it.

Chuck F via AccessMonster.com said:
Ok I tried the update query but I guess I was too retarded to figure it out
lol. I am using this beast for now and it seems to work.

Change Number: Mid([Field5],1,4) & IIf(Mid([Field5],5,10)="8005553333",
"8005551111",IIf(Mid([Field5],5,10)="8005554444","8005551111",IIf(Mid([Field5]
,5,10)="8005555555","8005551111",IIf(Mid([Field5],5,10)="8005551111",
"8005551111",IIf(Mid([Field5],5,10)="8005556666","8005556666",IIf(Mid([Field5]
,5,10)="8005552222","8005551111","NULL")))))) & Mid([Field5],15,43)

The original table looks like this:

Call Data
GMD 8005551111200507040127INQ 000001 5551234321
GMD 8005554444200507040848INQ 000001 5558487521
GMD 8005556666200507040900INQ 000001 5629088155
GMD 8005552222200507040903INQ 000001 3149650864

Finished table looks like this:

Change Number
GMD 8005551111200507040127INQ 000001 5551234321
GMD 8005551111200507040848INQ 000001 5558487521
GMD 8005556666200507040900INQ 000001 5629088155
GMD 8005551111200507040903INQ 000001 3149650864

So what I've done is changed all the numbers to 8005551111. 8005556666 also
stayed the same. I know it's a barbaric way of doing things but since I'm so
new to access and I need to make my life easier I'm trying to learn it as
quickly as I can.

Thanks,
I would use an update query.

Update [YourTableNameHere]
SET [Field5] = "8006666666"
WHERE [Field5] in ("8005555555","8007777777","8886667777")

You would have to do this for each value that you wanted to end up with.

Another method would be to build a table of original values and replacement
values and use it in a query to specify which values to replace with which
values. Something like

TheReplacementTable
OriginalValue - contents of 8005555555, 8006666666, etc
NewValue - contents of 8006666666 (etc.)

Then an update query of

UPDATE YourOriginalTable as O INNER JOIN TheReplacementTable as R
ON O.Field5 = R.OriginalValue
SET Field5 = R.NewValue
Hello,
NOOB HERE
Click to expand...
[quoted text clipped - 10 lines]
How can I do this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top