Trouble using the Replace Function

  • Thread starter Thread starter Dj
  • Start date Start date
D

Dj

I have a table that is imported. Field [Location] comes in as "Chicago, IL"
and I need to change that text to internal code "CHIL". I created an update
query that reads the Location and in the "update to" row of my [location]
field, I put the following.. Replace([location],"Chicago, IL","CHIL").

Am I using the funtion properly? Should I be using an update query? Thanks
in advance for any help. dozzee
 
That should work, but just to be sure, what's the SQL of the query? (If
you're not familiar with working directly with the SQL generated for a
query, while the query's open in Design view, select SQL view from the View
menu)

What happens when the query runs?
 
I am not familiar with the SQL view, but I did what you said and this is what
is says...
UPDATE [Working Table] SET [Working Table].Location =
Replace("location]","Chicago, IL","CHIL")

Again, that update expression is in the LOCATION column in the Update row
and when I run the Update Query, none of my Chicagos change to CHIL...just
the same old data pulls up.

Douglas J. Steele said:
That should work, but just to be sure, what's the SQL of the query? (If
you're not familiar with working directly with the SQL generated for a
query, while the query's open in Design view, select SQL view from the View
menu)

What happens when the query runs?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dj said:
I have a table that is imported. Field [Location] comes in as "Chicago,
IL"
and I need to change that text to internal code "CHIL". I created an
update
query that reads the Location and in the "update to" row of my [location]
field, I put the following.. Replace([location],"Chicago, IL","CHIL").

Am I using the funtion properly? Should I be using an update query?
Thanks
in advance for any help. dozzee
Click to expand...
Click to expand...
 
The quotes in there are incorrect. It should be:

UPDATE [Working Table] SET [Working Table].Location =
Replace([location],"Chicago, IL","CHIL")

Actually, you should probably only bother updating those rows where the
location contains Chicago, so something like

UPDATE [Working Table] SET [Working Table].Location =
Replace([location],"Chicago, IL","CHIL")
WHERE Location LIKE "*Chicago, IL*"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dj said:
I am not familiar with the SQL view, but I did what you said and this is
what
is says...
UPDATE [Working Table] SET [Working Table].Location =
Replace("location]","Chicago, IL","CHIL")

Again, that update expression is in the LOCATION column in the Update row
and when I run the Update Query, none of my Chicagos change to CHIL...just
the same old data pulls up.

Douglas J. Steele said:
That should work, but just to be sure, what's the SQL of the query? (If
you're not familiar with working directly with the SQL generated for a
query, while the query's open in Design view, select SQL view from the
View
menu)

What happens when the query runs?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dj said:
I have a table that is imported. Field [Location] comes in as "Chicago,
IL"
and I need to change that text to internal code "CHIL". I created an
update
query that reads the Location and in the "update to" row of my
[location]
field, I put the following.. Replace([location],"Chicago, IL","CHIL").

Am I using the funtion properly? Should I be using an update query?
Thanks
in advance for any help. dozzee
Click to expand...
Click to expand...
Click to expand...
 
I'm sorry to bother you again, but it's still not working. I've stripped
away all the excess and entered the SQL just as you sent it to me and it's
still not working. The query just returns the data as the original "chicago,
IL".

Can you possibly think of another reason this wouldn't be working for me?
Is there another option for gett the string replaced; perhaps a lookup
function?

Thanks again.

Douglas J. Steele said:
The quotes in there are incorrect. It should be:

UPDATE [Working Table] SET [Working Table].Location =
Replace([location],"Chicago, IL","CHIL")

Actually, you should probably only bother updating those rows where the
location contains Chicago, so something like

UPDATE [Working Table] SET [Working Table].Location =
Replace([location],"Chicago, IL","CHIL")
WHERE Location LIKE "*Chicago, IL*"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dj said:
I am not familiar with the SQL view, but I did what you said and this is
what
is says...
UPDATE [Working Table] SET [Working Table].Location =
Replace("location]","Chicago, IL","CHIL")

Again, that update expression is in the LOCATION column in the Update row
and when I run the Update Query, none of my Chicagos change to CHIL...just
the same old data pulls up.

Douglas J. Steele said:
That should work, but just to be sure, what's the SQL of the query? (If
you're not familiar with working directly with the SQL generated for a
query, while the query's open in Design view, select SQL view from the
View
menu)

What happens when the query runs?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a table that is imported. Field [Location] comes in as "Chicago,
IL"
and I need to change that text to internal code "CHIL". I created an
update
query that reads the Location and in the "update to" row of my
[location]
field, I put the following.. Replace([location],"Chicago, IL","CHIL").

Am I using the funtion properly? Should I be using an update query?
Thanks
in advance for any help. dozzee
Click to expand...
Click to expand...
Click to expand...
 
I finally figured out what I was doing wrong and I feel terribly, terribly
stupid. I never used an Update Query before and I didn't realized I had to
"run" it. I was flipping back and forth from my design view to my datasheet
view. Thanks for your help!

Dj said:
I'm sorry to bother you again, but it's still not working. I've stripped
away all the excess and entered the SQL just as you sent it to me and it's
still not working. The query just returns the data as the original "chicago,
IL".

Can you possibly think of another reason this wouldn't be working for me?
Is there another option for gett the string replaced; perhaps a lookup
function?

Thanks again.

Douglas J. Steele said:
The quotes in there are incorrect. It should be:

UPDATE [Working Table] SET [Working Table].Location =
Replace([location],"Chicago, IL","CHIL")

Actually, you should probably only bother updating those rows where the
location contains Chicago, so something like

UPDATE [Working Table] SET [Working Table].Location =
Replace([location],"Chicago, IL","CHIL")
WHERE Location LIKE "*Chicago, IL*"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dj said:
I am not familiar with the SQL view, but I did what you said and this is
what
is says...
UPDATE [Working Table] SET [Working Table].Location =
Replace("location]","Chicago, IL","CHIL")

Again, that update expression is in the LOCATION column in the Update row
and when I run the Update Query, none of my Chicagos change to CHIL...just
the same old data pulls up.

:

That should work, but just to be sure, what's the SQL of the query? (If
you're not familiar with working directly with the SQL generated for a
query, while the query's open in Design view, select SQL view from the
View
menu)

What happens when the query runs?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a table that is imported. Field [Location] comes in as "Chicago,
IL"
and I need to change that text to internal code "CHIL". I created an
update
query that reads the Location and in the "update to" row of my
[location]
field, I put the following.. Replace([location],"Chicago, IL","CHIL").

Am I using the funtion properly? Should I be using an update query?
Thanks
in advance for any help. dozzee
Click to expand...
Click to expand...
Click to expand...
 
Back
Top