Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Q and A _ Replace Function Vs?

Posted by: ben2203 Sep 10 2019, 07:46 AM

Hi Guys!

I have a field in a table which contains a bunch of data where the users have added . or .. or ... etc to circumnavigate various locks to prevent duplicate data, anyway, I need to remove those, my question is do I have to use the Replace function or can I use a query?

Thanks in Advance

Ben

Posted by: GroverParkGeorge Sep 10 2019, 07:56 AM

You can use Replace in an update query.
Or, you could use Replace in VBA to update those fields in a loop on a Recordset.

Posted by: Phil_cattivocarattere Sep 10 2019, 07:58 AM

Both: using Replace function in a query.
Be careful to have a backup table (or the whole database) if something goes wrong.Could you explain better what you intention using Replace? Do you have to delete all "dots" in a field?

Posted by: ben2203 Sep 10 2019, 08:12 AM

great stuff will check it out thanks

SELECT REPLACE(price, '.', '') AS price
FROM products;

I guess I can use select before I use update, the above as an example?

Posted by: orange999 Sep 10 2019, 08:26 AM

If removing the dots could result in duplication, you may need identify what exactly you should do in that case.
You indicate that the dot of various numbers were entered to avoid duplication. If that interferes with your storage or processing logic, better to have a plan before committing.

Posted by: ben2203 Sep 10 2019, 08:28 AM

How do I construct the update query

SELECT Replace(package_type_id,'.','') AS Package_Type_ID1, package_type_id
FROM tbl_generic_product;


Do I first create the above in a query and then link them using the primary key then update the tbl in that manner or is there a cleaner, quicker way to do it?

Thanks in Advance

Ben

Posted by: ben2203 Sep 10 2019, 08:33 AM

That's a good point. I currently have somebody working on a regex to restrict data entry to thai alphabet script not allowing numbers or punctuation so that should resolve the problem ultimately, just cleaning up the old data, reverted to deleting around 200,000 records and starting again. I will just delete the duplicates anyway as I should be using SQL server really with the web app but have just stuck with access.


Posted by: ben2203 Sep 10 2019, 08:40 AM

I think they do it to change the price, i've told them to differentiate the packaging with adjectives rather than random punctuation. I'll run the update after my mate has finished implementing a regex function to prevent punctuation and numbers being inserted into the text box control on the web form. See what happens, ended up deleting 200,000 odd records today just to clean things up so it should be clean soon enough.

Thanks for your help guys, I appreciate it, joined way back in 2005 and learned most from UtterAccess, good to see the community still going strong. I used to be addicted to this forum, it was the facebook of 2005.

Posted by: ADezii Sep 10 2019, 12:51 PM

  1. I believe that orange999 has a very valid point in that the removal of periods could, actually will, result in duplication.
  2. Why not perform the entire operation within a single Loop, namely: the removal of all periods and the deleting of any 'would be' Duplicates?
  3. Code Definition:
    CODE
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("tbl_generic_product", dbOpenDynaset)

    With rst
      If Not .BOF And Not .EOF Then
        Do While Not .EOF
          If InStr(![package_type_id], ".") > 0 Then
            If DCount("*", "tbl_generic_product", "[package_type_id] = '" & _
                      Replace(![package_type_id], ".", "") & "'") > 0 Then
              .Delete
            End If
          End If
            .MoveNext
        Loop
      End If
    End With

    rst.Close
    Set rst = Nothing
  4. tbl_generic_product 'before' Code Execution:
    IDpackage_type_id
    1ABC..UY.T7
    2ABC...UY..T7
    3ABC.UYT...7
    4ABCUYT7
    5GGY7..98U
    6GGY7.98U
    7GGY798U
  5. tbl_generic_product 'after' Code Execution:
    IDpackage_type_id
    4ABCUYT7
    7GGY798U


Posted by: ADezii Sep 11 2019, 07:12 AM

Sorry, forgot the other piece to this puzzle. Should the removal of Periods NOT result in Duplication then that Record needs to be Edited (have Periods removed) and saved. Code has been adjusted.

CODE
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tbl_generic_product", dbOpenDynaset)

With rst
  If Not .BOF And Not .EOF Then
    Do While Not .EOF
      If InStr(![package_type_id], ".") > 0 Then
        If DCount("*", "tbl_generic_product", "[package_type_id] = '" & _
                  Replace(![package_type_id], ".", "") & "'") > 0 Then
          .Delete
        Else    'Removal of Periods will not produce Duplicate, so remove them
         MsgBox ![package_type_id]
          .Edit
            ![package_type_id] = Replace(![package_type_id], ".", "")
          .Update
        End If
      End If
        .MoveNext
    Loop
  End If
End With

rst.Close
Set rst = Nothing

Posted by: ben2203 Sep 15 2019, 12:25 AM

Thanks,

Done it already, have a lock on the text box now so only text can be inputted. I appreciate the code example though, in the end I just made a query in access and have it running from a macro but this could come in useful too. I appreciate the help. Thanks again

Ben