UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Replace Function Vs?, Access 2007    
 
   
ben2203
post Sep 10 2019, 07:46 AM
Post#1



Posts: 888
Joined: 27-September 05
From: BKK


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

--------------------
-------------
Regards

Ben
Go to the top of the page
 
GroverParkGeorge
post Sep 10 2019, 07:56 AM
Post#2


UA Admin
Posts: 35,847
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Phil_cattivocara...
post Sep 10 2019, 07:58 AM
Post#3



Posts: 360
Joined: 2-April 18



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?
This post has been edited by Phil_cattivocarattere: Sep 10 2019, 07:58 AM

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
ben2203
post Sep 10 2019, 08:12 AM
Post#4



Posts: 888
Joined: 27-September 05
From: BKK


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?
This post has been edited by ben2203: Sep 10 2019, 08:20 AM

--------------------
-------------
Regards

Ben
Go to the top of the page
 
orange999
post Sep 10 2019, 08:26 AM
Post#5



Posts: 1,967
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


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.

--------------------
Good luck with your project!
Go to the top of the page
 
ben2203
post Sep 10 2019, 08:28 AM
Post#6



Posts: 888
Joined: 27-September 05
From: BKK


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

--------------------
-------------
Regards

Ben
Go to the top of the page
 
ben2203
post Sep 10 2019, 08:33 AM
Post#7



Posts: 888
Joined: 27-September 05
From: BKK


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.


--------------------
-------------
Regards

Ben
Go to the top of the page
 
ben2203
post Sep 10 2019, 08:40 AM
Post#8



Posts: 888
Joined: 27-September 05
From: BKK


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.

--------------------
-------------
Regards

Ben
Go to the top of the page
 
ADezii
post Sep 10 2019, 12:51 PM
Post#9



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


  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

Go to the top of the page
 
ADezii
post Sep 11 2019, 07:12 AM
Post#10



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


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
Go to the top of the page
 
ben2203
post Sep 15 2019, 12:25 AM
Post#11



Posts: 888
Joined: 27-September 05
From: BKK


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

--------------------
-------------
Regards

Ben
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th October 2019 - 11:09 AM