?
Solved

SQL: Cut & Paste to another field

Posted on 2005-05-12
22
Medium Priority
?
383 Views
Last Modified: 2012-05-05
I have a DB with a number of fields, but for the sake of this discussion let's say it only has three fields [Street],[City] and [Sub_City].
All of the records have data in both [Street] and [City] fields but for now [Sub_City] is empty.
In certain (not all) records the [Street] field contains the information that I would like to cut and paste into [Sub_City].
The [Street] information that I would like to remove from ALWAYS starts with "(" and ends with ")".

I need an SQL statement that can grab the text in between "(" and ")" and place it in the [Sub_City] field, and also delete all the text between and including the parentheses.

i.e.
Street = 123 main street (south boston)
City = Boston
Sub_City = [Null]

SQL=->

Street = 123 main street
City = Boston
Sub_City = south boston


Thanks,
BassKozz
0
Comment
Question by:basskozz
  • 11
  • 7
  • 3
  • +1
22 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13990318
This code merely pulls the [Sub_City] string, not including parenthesis.  The original data is untouched:

Public Function PopulateSubCity(strField as String) As String
Dim x, y

x = InStr(1, strField, "(")
y = InStr(x + 1, strField, ")")

PopulateSubCity = Mid(strField, x + 1, (y - x) - 1)

End Function

Now you can run a query to update the [Sub_City] values:

UPDATE MyTable SET [Sub_City] = PopulateSubCity([Street]) WHERE InStr(1, [Street], "(") > 0

And finally, run a query to update the [Street] values:

UPDATE MyTable SET [Street] = Replace([Street], "(" & [Sub_City] & ")", "") WHERE Nz([Sub_City], "") <> ""
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13990336
As you may notice, there is no error-checking to speak of in that routine.  You will likely receive an error related to invalid use of NULL, or invalid function/parameter.  As you said yourself, this will only handle particular records...I would recommend finding a query that pulls just those records, and use that as the source for your UPDATE statements.
0
 

Author Comment

by:basskozz
ID: 13990345
routinet,

is there anyway to run all of these at once ?

I need it to run all the records (5000+) ignoring the ones w/out parenthesis and scrapeing the ones that have them.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 12

Expert Comment

by:Preece
ID: 13990400
This may work with some modification:

Private Sub Command3_Click()
    Dim sStreet As String
    Dim sCity As String
    Dim sSubCity As String
    Dim lStartParens As Long
    Dim lEndParens As Long
   
    ' get rs
   
   
    ' loop thru each record
    Do While Not ors.BOF And Not ors.EOF
        ' loop thru recordset and populate variables one record at a time
        'sStreet = "23 main street (south boston)"
        'sCity = "Boston"
        'sSubCity = ""
        sStreet = ors!Street
        sCity = ors!City
        sSubCity = ors!Sub_City
       
        ' get parens positions
        lStartParens = InStr(1, sStreet, "(")
        lEndParens = InStr(1, sStreet, ")")
       
        ' if start parens existing, then parse out date
        If lStartParens <> 0 Then
            sSubCity = Mid(sStreet, lStartParens + 1, Len(sStreet) - lStartParens - 1)
            sSubCity = Trim(sSubCity)
            sStreet = Left(sStreet, lStartParens - 1)
            sStreet = Trim(sStreet)
           
            ' update record in db
        End If
       
        ors.MoveNext
    Loop
   
End Sub

Preece
0
 

Author Comment

by:basskozz
ID: 13990519
I should have mentioned this before but the table name is "Master"

Preece,
I couldn't get your code to work, could you modify it to work with the table name?

Also, this might require two functions:
1. copy - to copy the data from [Street] into [Sub_City]
           - copy text in between parenthesis
2. delete - Remove the data from [Street]
             - Remove the text in between and including the parenthesis

p.s. bumpped points to MAX, this question is thougher then i thought.

Thanks,
BassKozz
0
 
LVL 12

Expert Comment

by:Preece
ID: 13990596
How did you modify the code?  You'll obviously need to get a recordset.   Also, check for isnull:

        sStreet = iif(isnull(ors!Street), "", ors!Street)
        sCity = iif(isnull(ors!City, ors!City)
        sSubCity = iif(isnull(ors!Sub_City, ors!Sub_City)


0
 

Author Comment

by:basskozz
ID: 13990622
not familiar with getting a record set?
Why would I need to check for isNull ?...
The only field that should be empty (null) is the [Sub_City] field, because nothing has been placed into it.
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 2000 total points
ID: 13990662
Well, not at once, but you can run them one after the other.  Paste this sub into a module, and run it:

Public Sub DoUpdate()

CurrentDB.Execute "UPDATE MyTable SET [Sub_City] = PopulateSubCity([Street]) WHERE InStr(1, [Street], '(') > 0"
CurrentDB.Execute "UPDATE MyTable SET [Street] = Replace([Street], '(' & [Sub_City] & ')', '') WHERE Nz([Sub_City], '') <> ''

End Sub

Two things:

1) Make sure you paste the other sub I posted for you in the same module.  You'll need it for the query.
2) RUN IT ON A BACKUP FIRST!!!  If this does not do what you want, there is no way to roll it back.
0
 

Author Comment

by:basskozz
ID: 13990729
routinet,

Here is the code I put into the modual:
'<<<START>>>
Public Function PopulateSubCity(strField As String) As String
Dim x, y

x = InStr(1, strField, "(")
y = InStr(x + 1, strField, ")")

PopulateSubCity = Mid(strField, x + 1, (y - x) - 1)

End Function

Public Sub DoUpdate()

CurrentDb.Execute "UPDATE Master SET [Sub_City] = PopulateSubCity([Street]) WHERE InStr(1, [Street], '(') > 0"
CurrentDb.Execute "UPDATE Master SET [Street] = Replace([Street], '(' & [Sub_City] & ')', '') WHERE Nz([Sub_City], '') <> ''"

End Sub

'<<<END>>>

I got the following error message after running it:
Run time error '5':
Invalid procedure call or argument


and it pointed to this line of code:
PopulateSubCity = Mid(strField, x + 1, (y - x) - 1)



Please help,
Thanks.
0
 
LVL 12

Expert Comment

by:Preece
ID: 13990745
>  not familiar with getting a record set?

Sure, I get recorsets all day long!  The question is, are you familiar with getting a record set?  ;-)
0
 
LVL 51

Assisted Solution

by:Steve Bink
Steve Bink earned 2000 total points
ID: 13990823
Replace the sub with this:

Public Function PopulateSubCity(strField As String) As String
Dim x, y

x = InStr(1, strField, "(")
y = InStr(x + 1, strField, ")")

If ((x > 0) And (y > x)) Then
    PopulateSubCity = Mid(strField, x + 1, (y - x) - 1)
Else
    PopulateSubCity = ""
End If

End Function
0
 

Author Comment

by:basskozz
ID: 13991201
routinet,

it worked !!!
Thank you very much !

-BassKozz
0
 
LVL 7

Expert Comment

by:manthanein
ID: 13991219
please do try this  if you  have a use of it
some 4 months ago I created a database wherein I need to convert  "GT001 (STP1)"  to just "GT001"  I've done it using  query statement..  


first you need to extract  the data insde the ()
"Update partlist set Sub_City = mid(Street,instr(Street,'(') +1,instr(Street,')' - Street,'(') -2)  ) where Street like '*(*';"

this is the sql for removing the text inside the ()
"Update partlist set Street = mid(Street,1,instr(Street,'(') -1) where Street like '*(*';" \



but it's worth a try..  if it won't  work then I won't  bother  with this tread anymore
0
 

Author Comment

by:basskozz
ID: 14001315
routinet,

What would the code look like if I wanted to keep the Parentesis "()" and move everything including "()" to Sub_City ?

-BassKozz
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 14010329
Basically, it's a matter of tweaking the values on the line marked below.  I've already changed them in this copy to reflect keeping the parenthesis:

Public Function PopulateSubCity(strField As String) As String
Dim x, y

x = InStr(1, strField, "(")
y = InStr(x + 1, strField, ")")

If ((x > 0) And (y > x)) Then
    PopulateSubCity = Mid(strField, x, (y - x) + 1)   '<--- This line
Else
    PopulateSubCity = ""
End If

End Function
0
 

Author Comment

by:basskozz
ID: 14014863
routinet,

that worked, but it didn't remove the text from the [Street] Field, it just copyied it to [Sub_City] ???

-BassKozz
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 14019083
Right, but remember the second step from up above?

Public Sub DoUpdate()

CurrentDb.Execute "UPDATE Master SET [Sub_City] = PopulateSubCity([Street]) WHERE InStr(1, [Street], '(') > 0"
CurrentDb.Execute "UPDATE Master SET [Street] = Replace([Street], '(' & [Sub_City] & ')', '') WHERE Nz([Sub_City], '') <> ''"

End Sub


The first statement uses the custom function to populate the [Sub_City] field.  The second statement removes the now duplicate text from the [Street] field.
0
 

Author Comment

by:basskozz
ID: 14019975
routinet,
you there ?
-BassKozz
0
 

Author Comment

by:basskozz
ID: 14020371
It's still not removing the ([Sub_City]) from the [City]...
Here is what I have:
Public Function PopulateSubCity(strField As String) As String
Dim x, y

x = InStr(1, strField, "(")
y = InStr(x + 1, strField, ")")

If ((x > 0) And (y > x)) Then
    PopulateSubCity = Mid(strField, x, (y - x) + 1)   '<--- This line
Else
    PopulateSubCity = ""
End If

End Function


Public Sub DoUpdate()

CurrentDb.Execute "UPDATE tblMaster SET [Sub_City] = PopulateSubCity([Street]) WHERE InStr(1, [Street], '(') > 0"
CurrentDb.Execute "UPDATE tblMaster SET [Street] = Replace([Street], '(' & [Sub_City] & ')', '') WHERE Nz([Sub_City], '') <> ''"

End Sub


0
 

Author Comment

by:basskozz
ID: 14020376
BTW, I renamed my table from "Master" to "tblMaster"
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 14021439
My bad...forgot to account for the parenthesis in the query.  Use this sub instead.

Public Sub DoUpdate()

CurrentDb.Execute "UPDATE tblMaster SET [Sub_City] = PopulateSubCity([Street]) WHERE InStr(1, [Street], '(') > 0"
CurrentDb.Execute "UPDATE tblMaster SET [Street] = Replace([Street], [Sub_City], '') WHERE Nz([Sub_City], '') <> ''"

End Sub
0
 

Author Comment

by:basskozz
ID: 14023453
Thanks routinet, werks perfectly !!!

-BassKOzz
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question