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

vba_php

Forum Troll
Local time
Today, 05:29
Joined
Oct 6, 2019
Messages
2,884
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

Forum Troll
Local time
Today, 05:29
Joined
Oct 6, 2019
Messages
2,884
That code mite be too complex for him maj.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:29
Joined
May 21, 2018
Messages
8,463
That code mite be too complex for him maj
I am pretty confident Brocephus can handle a single line of code.
bo.jpg
 

pawson

New member
Local time
Today, 11:29
Joined
Mar 19, 2020
Messages
23
Before
Copy Of tblSoftware

IDsoftware
1​
Microsoft Professional 2019 - en.us-proof
2​
Microsoft Professional 2019 - en.en
3​
Microsoft Professional 2019 - es
4​
Microsoft Professional 2016 - us
Code:
UPDATE tblSoftware SET tblSoftware.software = cleanversion([software]);
after
tblSoftware

IDsoftware
1​
Microsoft Professional 2019
2​
Microsoft Professional 2019
3​
Microsoft Professional 2019
4​
Microsoft Professional 2016
Code:
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

You've got your good things, and you've got mine.
Local time
Today, 06:29
Joined
May 21, 2018
Messages
8,463
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

Immoderate Moderator
Staff member
Local time
Today, 05:29
Joined
Feb 28, 2001
Messages
26,996
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

Code:
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

New member
Local time
Today, 11:29
Joined
Mar 19, 2020
Messages
23
Thank you so much The_Doc_Man, it works very well. But now i have another question.
If i have the next table:
IDPRODUCT NAME
1Windows Professional 2016 - us.es
2Windows Office Professional 2019
3Windows Profesional 2019
4Windows 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

New member
Local time
Today, 11:29
Joined
Mar 19, 2020
Messages
23
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

Super Moderator
Staff member
Local time
Today, 10:29
Joined
Feb 19, 2013
Messages
16,553

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:29
Joined
Feb 28, 2001
Messages
26,996
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

You've got your good things, and you've got mine.
Local time
Today, 06:29
Joined
May 21, 2018
Messages
8,463
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

I’m here to help
Staff member
Local time
Today, 03:29
Joined
Oct 29, 2018
Messages
21,357
Thank you so much The_Doc_Man, it works very well. But now i have another question.
If i have the next table:
IDPRODUCT NAME
1Windows Professional 2016 - us.es
2Windows Office Professional 2019
3Windows Profesional 2019
4Windows 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

New member
Local time
Today, 11:29
Joined
Mar 19, 2020
Messages
23
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

You've got your good things, and you've got mine.
Local time
Today, 06:29
Joined
May 21, 2018
Messages
8,463
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:29
Joined
Feb 19, 2002
Messages
42,970
Rather than writing complex IIf statements in queries or writing functions, why not create a cross reference table? Two columns
OldValue, NewValue

Create a query that joins to the xref table on the OldValue field and the set statement changes the value to NewValue.

Easy :) Readable :) Modifiable without driving yourself crazy :)
 

Users who are viewing this thread

Top Bottom