I need to rename differents rows in the same field, but names are different.

[vba_php]

Why doesn't detect the string? It wants to update whole my rows. I don't know why?

Try using a variable in vba code and assign the replacement string to that vat. That woulf surely work.

 

[vba_php]

That code mite be too complex for him maj.

 

[MajP]

That code mite be too complex for him maj

I am pretty confident Brocephus can handle a single line of code.

 

[vba_php]

I am pretty confident Brocephus can handle a single line of code.
View attachment 80002

I was talking bout the finction sir.

 

[pawson]

Before
Copy Of tblSoftware

ID	software
1​	Microsoft Professional 2019 - en.us-proof
2​	Microsoft Professional 2019 - en.en
3​	Microsoft Professional 2019 - es
4​	Microsoft Professional 2016 - us

UPDATE tblSoftware SET tblSoftware.software = cleanversion([software]);

after
tblSoftware

ID	software
1​	Microsoft Professional 2019
2​	Microsoft Professional 2019
3​	Microsoft Professional 2019
4​	Microsoft Professional 2016

Public Function CleanVersion(strSoftware As Variant) As String
  If Not IsNull(strSoftware) Then
    CleanVersion = Trim(Split(strSoftware, "-")(0))
  End If
End Function

Any more questions Brochild?

Bro, sorry but i'm very noob in access and sql. Firstly i have to execute the the first code and then the second? I'm a little bit confused.
Isn't in my Sql view i think?

 

[MajP]

The first code is Sql. In the query window in the "update to" line put
cleanversion([software])
where software is your field name to update.

The function gets dropped into a standard module. Not a form's module or class module.

 

[The_Doc_Man]

IF the issue is that there is a string that contains a space, dash, and space in that order, and that you want to trim away everything to the right of that, and the idea of having a bit of code added to a module is too much for you at this time, then try this

UPDATE yourtable SET yourfield = LEFT( yourfield, InStr( 1, yourfield, " - " ) ) WHERE yourfield LIKE "* - *" ;

I'll break that down for you: For those strings that contain the space,dash,space sequence at all (the "LIKE" clause), identify the column where that three-character sequence starts (the InStr clause) and take only the characters to the left of that string (the LEFT portion of the clause).

 

[pawson]

Thank you so much The_Doc_Man, it works very well. But now i have another question.
If i have the next table:

ID	PRODUCT NAME
1	Windows Professional 2016 - us.es
2	Windows Office Professional 2019
3	Windows Profesional 2019
4	Windows Professional 2016 - en.proof

Now i want to remove the same as before, but i have to remove the word "Office" of the second product, and add the letter "s" in the third product. And the same as before, in just one query how can i do it? Is because I need to count each of the products and it's necessary to be named equal.
Thank you so much.

 

[pawson]

The first code is Sql. In the query window in the "update to" line put
cleanversion([software])
where software is your field name to update.

The function gets dropped into a standard module. Not a form's module or class module.

Thank you, but it's too difficult for me right now. But thanks

 

[CJ_London]

why don't you have a separate table for your product names, then your can replace your product name with a foreign key to that table. You have some work to do to get it done, but no more that you are doing now - and it won't be a problem going forward. See this link for pretty much the same question https://www.access-programmers.co.uk/forums/threads/name-maze.310270/#post-1678748

 

[The_Doc_Man]

Now i want to remove the same as before, but i have to remove the word "Office" of the second product, and add the letter "s" in the third product.

Doing three changes like this is not the work of a single query. While it is remotely possible to do such a thing, it would be nightmarish to compose it. You are asking a lot for SQL, when in fact is it is FAR easier and probably not that much slower to do that task as three queries executed one at a time. OR, if you only have one case where you want to add an "S" you might be better off doing that one manually. It would be faster than composing a query.

Also, products exist called "Windows Professional" and "Office Professional." They exist and are distinct products. Removing "OFFICE" changes the product name substantially to the point of it being a different product. Are you sure you didn't mean to remove "Windows" instead?

CJ_London's suggestion makes more sense in context if you have a LOT of these entries. If you only have a few then a manual edit coupled with CJ's idea is more efficient and is consistent with normalization practices. But it all depends on what else is in those tables and how often each possible value appears.

 

[MajP]

At this point simply just make a table with the wrong value and the value you want to see. Link by wrongvalue and run an update using new value. You keep adding exception.
WrongValue RightValue

 

[theDBguy]

Thank you so much The_Doc_Man, it works very well. But now i have another question.
If i have the next table:

ID	PRODUCT NAME
1	Windows Professional 2016 - us.es
2	Windows Office Professional 2019
3	Windows Profesional 2019
4	Windows Professional 2016 - en.proof

Now i want to remove the same as before, but i have to remove the word "Office" of the second product, and add the letter "s" in the third product. And the same as before, in just one query how can i do it? Is because I need to count each of the products and it's necessary to be named equal.
Thank you so much.

Hi. If all you need to do is "count" each product, I am not sure the name has to be equal, and therefore, you probably don't need to use an UPDATE query at all. For example, in your sample table above, is the product count going to be 1 or 2?

 

[pawson]

At this point simply just make a table with the wrong value and the value you want to see. Link by wrongvalue and run an update using new value. You keep adding exception.
WrongValue RightValue

thanks, i made what you said, but there are some registers that disappear (just 3 registers), but i don't know why. I think in this way works, but some suggest about this

 

[MajP]

but there are some registers that disappear (just 3 registers),

I am guessing at what that means. I am guessing you expect to get a join but you are not getting one. Then either you have a typo or the real data has a space or spaces.