Replace chr in multiple fields

[RogueVW]

Hello,

I have a table that is imported daily and I need to validate the data and delete the tilde ~ in several fields before it is appended to another table.

I have been using the find and replace dialog box to do this but then I need to select each field individually and find and replace all. This is time consuming as there are many fields.

The only string that needs to be deleted is the tilde and it is always in a field by itself and not in a string with other characters.

I have tried using the update query but since the tilde is in many fields this didn't seem to be the way to do this.

I know that there is a replace() function but I am unsure of how and where to implement it. I am using Access 2000

TABLE RAWINQUIRES
ID FIRST  GEN   DISC  ADV  CALL SEND  DOUBT  SUB
1  Mike    1     ~     ~    Y    N     ~      ~
2  john    1     1     RX   N    Y     T      N
3  sally   0     2     ~    ~    N     T      Y
4  Jenny   ~     ~     BV   ~    F     ~      ~

Many thanks,

Jon

 

[NVSbe]

Try this code:

Dim rst As Recordset
Dim cmd As String
Dim x As Byte

cmd = "select * from tbltest"
Set rst = CurrentDb.OpenRecordset(cmd)
rst.MoveFirst
Do While Not rst.EOF
For x = 1 To rst.Fields.Count - 1
If rst.Fields(x) = "~" Then
rst.Edit
rst.Fields(x) = "new value"
rst.Update
End If
Next
rst.MoveNext
Loop
Set rst = Nothing
--------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[RogueVW]

Hello,

I'm getting a type mismatch error 13 and the debuger halts at:

If rst.Fields(x) = "~" Then

any ideas?

 

[NVSbe]

Probably a numeric field. I forgot to check for those... Try adding a test on that

Dim rst As Recordset
Dim cmd As String
Dim x As Byte

cmd = "select * from tbltest"
Set rst = CurrentDb.OpenRecordset(cmd)
rst.MoveFirst
Do While Not rst.EOF
For x = 0 To rst.Fields.Count - 1
If rst.Fields(x).Type <> dbNumeric Then
If rst.Fields(x) = &quot;~&quot; Then
rst.Edit
rst.Fields(x) = &quot;new value&quot;
rst.Update
End If
End If
Next
rst.MoveNext
Loop
Set rst = Nothing
--------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[RogueVW]

Yep, that did it many thanks for your help

 

[NVSbe]

No problem =) --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.