Search-Replace code for table Access97 1

[lukasikrob]

I would like to search and than replace values in Access table is there a code for this so I can use it in module?

 

[LittleSmudge]

Yes,

Generate an update query in a SQL text string and then use Do.Cmd.RunSQL to run it.


Eg.

Dim strSQL As String

strSQL = "UPDATE tblCategory " _
       & "SET tblCategory.CatName = 'Hinge' " _
       & "WHERE tblCategory.CatName = 'Bracket' "

DoCmd.RunSQL strSQL



'ope-that-'elps.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[scking]

That would work fine if the only text in the field were what needed to be replaced but if you need to replace a value within a string of text characters then you need to do a little more.

Sub ReplaceThis(MyValueToReplace As String, ReplaceWith As String)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT tbl.* FROM tbl;&quot
If rst.RecordCount <> 0 Then
rst.MoveFirst
Do While Not rst.EOF
If InStr(1, MyValueToReplace, rst!Field1) Then
rst!Field1 = Replace(rst!Field1, MyValueToReplace, ReplaceWith)
End If
DoEvents
rst.MoveNext
Loop
End If

OR open the table and use Find and Replace.
OR you could probably even open the Find and Replace dialog from code.
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[beetee]

That would work fine unless you wanted to search and replace ALL fields in the table... ok, just kidding, but you probably want to iterate through the fields, possibly checking field types...

Sub ReplaceTheHeckOutOfThis(MyValueToReplace As String, ReplaceWith As String)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(&quot;SELECT tbl.* FROM tbl;&quot
If rst.RecordCount <> 0 Then
rst.MoveFirst
Do While Not rst.EOF
Dim CurField As Field
For Each CurField In MyRst.Fields
If InStr(1, MyValueToReplace, Nz(CurField, &quot;&quot) Then
rst!Field1 = Replace(CurField, MyValueToReplace, ReplaceWith)
End If
Next CurField
DoEvents
rst.MoveNext
Loop
End If

 

[beetee]

incidentally, just after I hit submit I noticed a reference to rst!Field1... please change all those to CurField

 

[lukasikrob]

OK
Thanks a lot all of you.
All the solutions are very good but,
All I want to do is Replace all the zeros with a null values. All the fields which I need to change are numeric and I dont want to change them to a string or text.
I also dont want to change all the fields.
All toghether there is 63 fields 506 records and I would like to change 60 fields to Null, if zero.
I could create 60 queries and Update to null if zero a field at a time but surely there is a better way of doing this.
Is there?
Thanks again for the postings.

 

[beetee]

restating:

you have a record with 63 fields. 60 of those fields, you want to change nulls to zeros. You don't want to write 60 update queries.

The code we showed you already could have been easily modified to suit your needs; given a bit of time studying the Access help.

Sub ReplaceThis()
Dim MyRst As DAO.Recordset
Set MyRst = CurrentDb.OpenRecordset(&quot;SELECT tbl.* FROM tbl;&quot
If MyRst.RecordCount <> 0 Then
MyRst.MoveFirst
Do While Not MyRst.EOF
Dim Edited As Boolean
Edited = False
Dim CurField As Field
For Each CurField In MyRst.Fields
Select Case CurField.Type
Case dbInteger, dbLong, dbDouble
Select Case CurField.Name
' field names to exclude from 0 to null conversion
Case &quot;Exclude1&quot;, &quot;Exclude2&quot;, &quot;Exclude3&quot;
Case Else
If Not IsNull(CurField) Then
If CurField = 0 Then
If Not Edited Then
MyRst.Edit
Edited = True
End If
CurField = Null
End If
End If
End Select
End Select
Next CurField
If Edited Then MyRst.Update
DoEvents
MyRst.MoveNext
Loop
End If
End Sub

 

[lukasikrob]

BeeTee
At first thanks a lot it does work with no problem at all.
As to &quot;The code we showed you already could have been easily modified to suit your needs&quot; well, for the programmer it is propeably very true.
And finally if You know of a way to learn VBA, so I can understand it (Very big accent on I) and in reasonable short time could write VBA codes please let me know. I would appreciate that.
Thanks again for the solution.

 

[beetee]

I guess the feeling is, if you're going to use code, you should know code.

What if the code I sent you incorectly erased records from your database?

Regarding learning VBA, the best way is to start with small, simple examples and build up. I never found books of much use; the best way is to use the Access help and start hacking. Of course, if you have no interest in VBA, you may not want to do this. But then, you really shouldn't be incorporating VBA in your projects.

 

[lukasikrob]

Hi
This is a last posting about this issue.
I do agree with you that I should know the code, but don't forget that if your code did something which it should not, it would do that to my test table, and I would see that immedietely.
Also sometime I don't have a choice but to use the VBA, you must know that Macros,Queries,Forms often will not do what you want and VBA is the only option to take.
Thanks for the tips and again for the code.
Robert.