Update the text field

[BorisGomel]

Hello all of you,



I hope somebody help me with this issue. On my job I work with large db that process payments for many attorney offices. The problem I have that some checks we send to regular court, another checks we sent to superior court of the same county based on its filing status. Here the below example:

Status dD Court
Opening case OC Broward County Clerk of Court
Satif of Judment ST Broward Superior County Clerk of Court

We work with 24 states and I have 4256 counties in table “counties”.
I have to add 4256 counties that are all the same with adding only word "Superior" after name of the county before word “County" in record so I can print checks correctly .I don't want to type 4256 counties .I made a copy of “counties” table and what I need to enter word” Superior" before “County” word in all records of copy of table “county” and append it back to County table.

I am really appreciated for help. It would safe me a lot time and labor.

Thank you very much in advance,

The Best Regards,

BorisGomel

[Dal Jeanis]

I assume you meant you wanted "Superior " added before the word "Court".

Here's a sample.

Best practices: Back up your database before you try it.

UPDATE tblSupCounty
SET tblSupCounty.xName = Replace(tblSupCounty.xName,"Court", "Superior Court");

[rpeare]

Create a copy of your current COUNTIES table
Rename it SUPERIOR_CONVERSION
Run an update query on the table SUPERIOR_CONVERSION on the description field so for instance if your description is ALWAYS

Orange County Clerk of Court

and you ALWAYS want the result

Orange Superior County Clerk of Court

Let's say the field name is 'Court'

So your UPDATE query you'd add SUPERIOR_CONVERSION table
Put in the COURT field
in the UPDATE TO field you would have


Left([Court],InStr([Court]," ")) & "Superior " & Right([Court],Len([Court])-InStr([Court]," "))

Run the query and VERIFY THE RESULTS

This will only work if ALL of your descriptions are as you have indicated where the county comes first and you ALWAYS want to insert the word superior after the first word of the current court name.

If the results are what you want, paste the results back into your COUNTIES table.

BE AWARE. If you have data that is currently storing the COUNTYCOURTID (assuming you have a PK in your county court table) it will not be updated you will only be able to reference these 'new' courts going forward unless you update old data.

[ssanfu]

From your examples, I think the query that Dal provided should be changed to:

UPDATE counties
SET counties.Court = Replace(counties.Court,"County", "Superior County");

So you would
- create a copy of the table "Counties"
- run the query
- append the changed records from the "copy of counties" to the original "counties' table.

Short and sweet....



I tend to use VBA; here is what I came up with. Like Dal said, try this on a copy of the database.
Put this code in a standard module and press the F5 key.

Private Sub AddName()
   Const CN As String = "Superior "

   Dim db As DAO.Database
   Dim srs As DAO.Recordset
   Dim drs As DAO.Recordset
   Dim sSQL As String
   Dim tmp As String

   Dim x As Integer
   Dim k As Integer
   Dim j As Integer
   Dim RC As Long
   Dim varReturn

   Set db = CurrentDb

   'open target recordset
   sSQL = "SELECT Status, dD, Court FROM counties"
   Set drs = db.OpenRecordset(sSQL, , dbAppendOnly)

   'open source recordset
   sSQL = "SELECT Status, dD, Court FROM counties ORDER BY Court"
   Set srs = db.OpenRecordset(sSQL, , dbReadOnly)

   srs.MoveLast
   RC = srs.RecordCount
   srs.MoveFirst

   k = 0
   j = 0

   Do
      k = k + 1
      varReturn = SysCmd(acSysCmdSetStatus, "Record " & k & " of " & RC & " / " & j & " records appended ")
      x = 0
      tmp = srs!court
      x = InStr(1, tmp, CN)
      If x = 0 Then

         tmp = Replace(tmp, "County", "Superior County")

         sSQL = "INSERT INTO counties ( Status, dD, Court )"
         sSQL = sSQL & " VALUES ('" & srs!Status & "', '" & srs!dD & "', '" & tmp & "');"
         'Debug.Print sSQL
         db.Execute sSQL, dbFailOnError

         j = j + 1
         varReturn = SysCmd(acSysCmdSetStatus, "Record " & k & " of " & RC & " / " & j & " records appended ")

      End If
      srs.MoveNext
   Loop Until srs.EOF

   MsgBox "done"
   varReturn = SysCmd(acSysCmdClearStatus)

End Sub

[Dal Jeanis]

Steve -

I think Boris is really trying to make the check out to the "Broward County Clerk of Superior Court" rather than "Broward Superior County Clerk of Court", assuming that we're talking about counties in the United States. That's why I stated my assumption and coded it as stated. But, you're right, your code will do exactly what Boris ASKED for, if he first changes "counties" (the real table name) to "county", his temporary copied table name.

A quick google tells me that most of those mass changes won't be right, but knowing the way that checks are handled in real life, it won't matter - they would happily deposit checks made out to "Browurd Cnty Clerk of Forman".

Boris - If any of these examples work for you, please mark the thread "solved". Thanks!

[BorisGomel]

Thank you very much.
I got it !!!!!!!!!!
The Best Regards,
BorisGomel

[Dal Jeanis]

Awesome, Boris!