Using Update query to change a part of a value?

[Ms Rusty Boyd]

I am working in MS Access 2003 and I want to use the update query to change part of a value in a field. I have a last name field that contains a lot of records. Someone entered the values De Pedro and De La Rosa etc. and my boss wants them in lower case de Pedro and de la Rosa. I want to update De to de without having to manually change them. I tried criteria Like *De* and update *"de"* but the whole name is replaced with de. How can I replace De with de without overwriting the rest of the name? I would appreciate your expertise. Rusty

[DeMan]

Hi Ms Rusty Boyd,

I'll just move this thread to the access forum, where better qualified experts are more likely to see it......

I hope they can help you with your enquiry

[Ms Rusty Boyd]

Hi Ms Rusty Boyd,

I'll just move this thread to the access forum, where better qualified experts are more likely to see it......

I hope they can help you with your enquiry

I appreciate your assistance, I am new to this environment. Rusty

[JKing]

Start a new query and enter sql view. Enter the below SQL replacing tblTable with the name of the table you want to update and lastName with the field name you want to update. Access will prompt you asking if it's alright to change x amount of records. After completion just change "de Pedro" and "De Pedro" to "de la Rosa" and "De La Rose" respectively.

UPDATE tblTable SET tblTable.lastName = "de Pedro"
WHERE (((tblTable.lastName)="De Pedro"))
 

[Ms Rusty Boyd]

Start a new query and enter sql view. Enter the below SQL replacing tblTable with the name of the table you want to update and lastName with the field name you want to update. Access will prompt you asking if it's alright to change x amount of records. After completion just change "de Pedro" and "De Pedro" to "de la Rosa" and "De La Rose" respectively.

UPDATE tblTable SET tblTable.lastName = "de Pedro"
WHERE (((tblTable.lastName)="De Pedro"))
 

How can I do this with wildcards? I want it to find DE no matter what last name it is included in and convert it to "de" while leaving the rest of the name in tact. Thank you for your interest. Rusty

[JKing]

Alright this should be more along the lines of what you want.

UPDATE tblTable SET tblTable.lastName = Replace([tblTable]![lastName],"De","de")
WHERE (((tblTable.lastName) Like "De*"));
 

The Replace Function's parameters are (stringExpression, find, replace)
Searches the first string for the second string and replaces the second with the third string within the first string.

Try that and let me know how it turn's out.

[Ms Rusty Boyd]

Alright this should be more along the lines of what you want.

UPDATE tblTable SET tblTable.lastName = Replace([tblTable]![lastName],"De","de")
WHERE (((tblTable.lastName) Like "De*"));
 

The Replace Function's parameters are (stringExpression, find, replace)
Searches the first string for the second string and replaces the second with the third string within the first string.

Try that and let me know how it turn's out.

I most definitely will try it. Thank you.

[JKing]

I overlooked one small aspect. You need to add a space in LIKE in the where clause

WHERE (((tblTable.lastName) Like "De *"));
 

Otherwise any names such as Del'Monte or Demitri would also have the d put to lower case.

[Ms Rusty Boyd]

Alright this should be more along the lines of what you want.

UPDATE tblTable SET tblTable.lastName = Replace([tblTable]![lastName],"De","de")
WHERE (((tblTable.lastName) Like "De*"));
 

The Replace Function's parameters are (stringExpression, find, replace)
Searches the first string for the second string and replaces the second with the third string within the first string.

Try that and let me know how it turn's out.

I got the reply You are about to update 0 records. Back to the drawing board.

[Ms Rusty Boyd]

I overlooked one small aspect. You need to add a space in LIKE in the where clause

WHERE (((tblTable.lastName) Like "De *"));
 

Otherwise any names such as Del'Monte or Demitri would also have the d put to lower case.

I copied and pasted your code and replaced the table and field name but it still said "You are about to replace 0 records."

[JKing]

Can you paste me your sql? That last one should work I tested it out with the space.

[Ms Rusty Boyd]

I overlooked one small aspect. You need to add a space in LIKE in the where clause

WHERE (((tblTable.lastName) Like "De *"));
 

Otherwise any names such as Del'Monte or Demitri would also have the d put to lower case.

I would love to know how to do this in a query, but what you said about adding the space gave me a clue to a low-tech solution. I went to Find & Replace and set the Find to space De space and the de done the same way and it worked. Thank you for setting me on a productive path. If you come up with an Update query solution I would still appreciate knowing how to change some of the value while leaving the rest in tact. Thanks again, Rusty

[Ms Rusty Boyd]

Can you paste me your sql? That last one should work I tested it out with the space.

UPDATE Cities SET Cities.LName = Replace([Cities]![LName],"De","de")
WHERE (((Cities.LName) Like "De *"));

Here it is...

[Ms Rusty Boyd]

Can you paste me your sql? That last one should work I tested it out with the space.

I couldn't let it go after you said yours worked, you're right. I put a space after the first astrisk also "* De *" and it did work. Thank you so so much. Rusty

[JKing]

Great! I'm glad things worked out.

JKing

[rokgod7]

@Ms Rusty Boyd
This thread was very helpful, and I was able to accomplish my task, which was similar to the original question. If you don't want to use the SQL view, but instead want to do it in the design view, you can do it in the SQL view once, and then switch over to the design view, and see how it did it for you. From there, you can use that methodology, and modify it as needed.