Struggling with creating the Replace function 1

[John1Chr]

I cannot get my code to work. I am fairly new to visual basic. I just want to remove the parentheses out of about 25,000 fields from a query that I am basing off of a warehouse table. See thread705-1277401 for full explanation. Some of the records have parentheses and some don’t. I am creating a function and placing it in the query and I cannot get it to work.

I want these results:
2604-(26X03) should be 2604-26X03
4610-(96719) should be 4610-96719
5618-(5025) should be 5618-5025
2103-(95108) should be 2103-95108
5622-12 should be 5622-12 (unchanged)
5628-3 should be 5628-3 (unchanged)
4402-881 should be 4402-881 (unchanged)

This is the code that I have written and I have placed this function in the query:
Option Compare Database
Public Function NewParse(SP_NBR As String) As String
Dim NS As String

NS = SP_NBR
NS = Replace(Replace(NS, "(", ""), ")", "")

NewParse = NS

End Function

 

[traingamer]

What does your update query look like?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[John1Chr]

UPDATE PUBLIC_PPMS_PM_SP_NUMBERS SET PUBLIC_PPMS_PM_SP_NUMBERS.SP_NBR = NewParse([SP_NBR])
WITH OWNERACCESS OPTION;

 

[mp9]

Instead of writing your own function for this so that you can replace both open and closed paranethese in one hit, why not just run two consecutive UPDATE queries:

UPDATE PUBLIC_PPMS_PM_SP_NUMBERS SET SP_NBR = Replace(SP_NBR, "(", "")


UPDATE PUBLIC_PPMS_PM_SP_NUMBERS SET SP_NBR = Replace(SP_NBR, ")", "")

 

[John1Chr]

MP9, That doesn't seem to work. (unless I am doing something wrong.) See the SQL below. I am running it off a warehouse (ODBC) table. (If that matters.) I am thinking that I need to have a function to get it to work

UPDATE PUBLIC_PPMS_PM_SP_NUMBERS SET PUBLIC_PPMS_PM_SP_NUMBERS.SP_NBR = Replace("SP_NBR","(","");

 

[Golom]

Actually you don't need your own function. You can do it all in the SQL.

UPDATE PUBLIC_PPMS_PM_SP_NUMBERS 

SET SP_NBR = Replace(Replace(SP_NBR, "(", ""), ")", "")

 

[John1Chr]

Gollum,

I copied it into the SQL of access and when I run it I still see the brackets in it. What am I not doing that I should be?

 

[Golom]

If you are copying it into a query and then running the query within query designer then note that query designer does not run action queries as coded while you are within query designer.

The code is translated to a SELECT statement that shows you the records that would be affected but it does not change them.

You need to
- Copy it to a query
- Save the query
- Exit query designer
- Double-click on the saved query

and that will run the UPDATE rather than just showing you records.

 

[PHV]

Click the ! button instead of the datasheet view icon.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[John1Chr]

Gollum,

I am running against a read only table. (I apologize for not mentioning that.) Anyways, I decided to make a table and I just added a 2 to the end of the table name. I am running your query against it and I am now getting a Compile error. in query expression. Here is my sql.


UPDATE PUBLIC_PPMS_PM_SP_NUMBERS2 SET PUBLIC_PPMS_PM_SP_NUMBERS2.SP_NBR = Replace(Replace(SP_NBR,"(",""),")","");

 

[Golom]

Try brackets around your table and field names.

UPDATE [PUBLIC_PPMS_PM_SP_NUMBERS2] 
SET [SP_NBR] = Replace(Replace([SP_NBR],"(",""),")","")

although it shouldn't matter ... PUBLIC is a reserved word in Access.

 

[John1Chr]

I am getting the message Compile error.in query expression 'Replace(Replace([SP_NBR],"(",""),")","")' when I actually run the query. Whew, I am thinking of taking a lunch break!

 

[Golom]

What version of Access are you running?

The Replace function did not exist in Access 97 or earlier.

If it is A'97 then use

Public Function A97Replace(ByVal TheString As String, _
                           ByVal FindThis As String, _
                           ByVal ReplaceWith As String) As String
    Dim n                           As Long
    If Len(FindThis) > 0 And Len(TheString) > 0 Then
        n = InStr(1, TheString, FindThis)
        Do Until n = 0
           TheString = Left(TheString, n - 1) & ReplaceWith & _
                       Mid(TheString, n + Len(FindThis))
           n = InStr(n + Len(ReplaceWith), TheString, FindThis)
       Loop
    End If
    A97Replace = TheString
End Function

 

[John1Chr]

I am using access 2000. SP_NBR is actually text. (I'm not sure if that matters or not.) When I run the query I get a compile error message. Am I driving u nuts, yet?

 

[Golom]

Am I driving u nuts, yet?

No more than I already am.

There's a possibility that you have a user-defined function called "Replace" that's not compatible. Force the use of the intrinsic VBA one

UPDATE [PUBLIC_PPMS_PM_SP_NUMBERS2] 
SET [SP_NBR] = [red]VBA.[/red]Replace([red]VBA.[/red]Replace([SP_NBR],"(",""),")","")

 

[John1Chr]

I am still getting that compile error. I wonder if it would work to try and parse it out to another new field or if there is another command to break up the field and put it into a new one. I didn't think that it would be that difficult to get rid of the parentheses.

If you want, I can zip up the database and send it to you and let you try and figure it out.

 

[John1Chr]

Gollum,

I posted the actual database in the dbforums.com. My handle is John2Chr.

Thanks for your effort!

 

[Golom]

OK.

Delete Module3 and Module5. They have errors in them that are interfering with compilation.

Also remove one of the instances of [blue]Option Compare Database[/blue] from module [blue]OIMdbase[/blue]. You have it in there twice and that's also generating an error.

After you've done that then Query2 runs.

 

[John1Chr]

Thanks Golom,

I apolgize; I mispelled your name earlier as Gollum, like that freaky character in Lord of the Rings. I had no idea that the other modules would cause problems within the database. It worked just as you said.

 

[Golom]

Whenever you run a query, Access goes through a compile because you might be referencing User-Defined Functions in the SQL and those need to be compiled versions.