Removing characters (1 Viewer)

[Rick]

I have a "User" field in a table that gets populated from an imported file. The data in the field looks like the following:

MT<>PWI<>Smith
MT<>PWI<>Jones
MT<>PWI<>Doe

How do I strip the MT<>PWI<> from the record(s). This will also need to work on our monthly imports. Thanks in advance

 

[bradcccs]

Is the "MT<>PWI<>" portion of name constant? Without exceptions?

 

[AncientOne]

Use an Update query with the "Update to line" set to:

Right([USER],Len([USER]-9))

This will strip the first 9 characters from the string. This will only work if the characters to be stripped are always the first nine.

 

[bradcccs]

My line of thought exactly.

However, do not run the update query on the same table more than once unless you have a criteria statement active, or you will strip a name a second time.

ie:
MT<>PWI<>Smithopolous
(1st action) gives
Smithopolous
(2nd time) gives
ous (First nine characters removed)

if the pre-string is constant, you could use the following criteria:

Like ("MT<>PWI<>*")

Brad.

 

[AncientOne]

Yes, definitely add the criterion (It goes in the criteria line of the grid, not in the Update line: sorry if this seems too elementary)

 

[harra]

If you are using Access2000 or greater you get the use of the Replace() function. YOu can do something like this:

Replace([User],"MT<>PWI<>","")

This will replace any instances of MT<>PWI<> with a NullString. Plus, you don't need to worry about executing the Update Query more than once since it will only "clean" the field if the MT<>PWI<> string exists

 

[bradcccs]

Nice!

 

[Rick]

Sorry for the dumb question Harra but, Where would I insert this statement? Would I create a Module for this?? Again, I apologize, I am still learning. Thanks to all for the responses.

 

[harra]

Rick,

Just like AncientOne suggested you need to put it on the "Update To" line of the Query Builder. This should be the easiest way to clean up this data.

 

[Rick]

It's me again, When I attempt to run the "Replace" statement in the "Update" query I get the following error: Undefined function REPLACE in expression.

What am i doing wrong???

 

[harra]

Rick,

It's not working because stupid me ASSUMED that replace was a function was available to a query.

What I did was create a public function in a global module. For simplicity sake I named it ReplaceUQ because you are using the same paramter syntax, but it is a user-defined function.

Public Function ReplaceUQ(strText As String, strFind, strReplace) As String
    ReplaceUQ = Replace(strText, strFind, strReplace)
End Function

Then you can use the following syntax in the "Update To" section of your UPDATE Query:

ReplaceUQ([User],"MT<>PWI<>","")

I tested it this time and it works for me in Access2000. I am assuming you are using an Access version 2000 or higher because this won't work with Access97

 

[bradcccs]

I am using AccXP and was able to use the replace statement inside the update query without issue.

Thanks harra, that could become handy.

 

[harra]

Maybe AccessXP opened up some more Functions that can be used within a Query. Good to know!