erase characters at the end

[bjelinski]

I have data that represents certain ID CODE. This ID CODE has some unnecessary characters. The last star with letters (i.e. *DG or *HSL etc. ) should be removed.

In attachment I have included a mock up of the sample data. The important part is ID CODE column. As you can see there are more stars in the code, but the relevant part is to erase the last star together with the letters after the star.
Example: This ID CODE - 63047450201*RO*Kolis*RBBG*LIM would become THIS 63047450201*RO*Kolis*RBBG

I did some trial and error , and created another query and used mid formula, but this simple approach is not dynamic. Because we dont know on what position is the last star (*).



Thanks in advance,

Bjelinski

[NTC]

manipulating text strings is an interesting project. You are on the right track with the mid function.

in the end you are going to need to create a mix of Mid, Trim, Count.....you can count to the first * and then once that number is established - use that number in your Trim or Mid functions.

its tricky but do-able..... some prefer to do it all inside vb - - but I've done it successfully in a query with side by side columns doing sections of the logic so that one can see the result at each step; which helps tweak it to be correct for every variation.....

[Rawb]

Here's a function that starts at the end of a String and counts backwards until it finds an asterisk (character 42). Then it cuts off everything from that point to the end of the string.

  Dim intCurrChar, intTrimAt as Integer

  intTrimAt = -1
  intCurrChar = Len([ID CODE]) - 1

  Do While intCurrChar > 0
    If Left([ID CODE], intCurrChar, 1) = Chr(42) Then
      intTrimAt = intCurrChar
      intCurrChar = 0
    End If

    intCurrChar = intCurrChar - 1
  Loop

  If intTrimAt > -1 Then
    [ID CODE] = LEFT([ID CODE], 0, intTrimAt)
  End If

You'll need to figure out the parts in Bold, but everything else should work.

[pbaldy]

The last star can be found easily with the InStrRev() function.

[Rawb]

The last star can be found easily with the InStrRev() function.

Well yeah. . .

If you wanna do it the easy way.

[NassauBob]

Well yeah. . .

If you wanna do it the easy way.

Yeah, where's the fun in that? LOLOL

[pbaldy]

Sorry; it's my extreme laziness rearing its ugly head.

[bjelinski]

Hello,

thank you on valuable responses. However I am still trying to find a way to make the formula in a new query that would be able to the above task.

In excel I used the following formula, and now I want to make some type of similar thing in access.

=LEFT(C2;FIND("^^^";SUBSTITUTE(C2;"*";"^^^";LEN(C2 )-LEN(SUBSTITUTE(C2;"*";""))))-1)

D2 is the part where ID_CODE starts.

For find function I wanted to use InStrRev and for substitute I wanted to use replace. Hm but I am not sure, because access even changes the places of parameters required

[maximus]

I have created a function below which will remve the *and last three letters.

I am attaching a sample mdb file. In it you will find a query named Query 1
run it and the data in you table will change.


Option Compare Database
Dim strmyString As String
Dim intLength As Integer
Dim intExitIndex As Integer
Function AdjustedString(strmyString) As String
If IsNull(strmyString) Then
Exit Function
End If

intLength = Len(myString)
intExitIndex = InStrRev(strmyString, "*")

AdjustedString = Left$(strmyString, intExitIndex - 1)
End Function

[bjelinski]

Thanks for the help, but it tells me unrecognized database format?!

[maximus]

check this one

[bjelinski]

I don’t know why? But I still get the message? strange

[Rawb]

I can open it just fine. And since you have a newer version of Access than I do, you shouldn't be having any trouble.

Strange.

[maximus]

The most strange thing is that its the database that you had posted.

Well put the function in a module and then in the sql view of a query copy paste this:

UPDATE Data SET Data.[ID CODE] = AdjustedString([ID Code]);
then save the query and run it.

Now you don't need to download the attached mdb.

[bjelinski]

Thanks for the effort! everything worked fine! In the process I learned few new things, that part is of course priceless